SP Help

[DBA]

I have this SP(sp__spaceused) I am trying to fix the o/p
where it says Log Pages: log size 0 MB

But when we view it using DBartisan is shows 0.02 MB for the
DB used and even when you run the sp_helpsegment logsegment
it shows used_pages as 241 it’s very similar to ase 15
sp_spaceused but had to be modified for this shop .

create procedure sp__spaceused
@objname varchar(317) = null, /* the object we want size
on */
@list_indices int = 0 /* don't sum all indices, list each
*/
as

declare @type smallint /* the object type */
declare @msg varchar(1024) /* message output */
declare @dbname varchar(255) /* database name */
declare @tabname varchar(255) /* table name */
declare @length int

if @@trancount = 0
begin
set chained off
end

set transaction isolation level 1

/*
** Check to see that the objname is local.
*/
if @objname is not null
begin
/*
** Get the dbname and ensure that the object is in the
** current database. Also get the table name - this is later
** needed to see if information is being requested for
syslogs.
*/
execute sp_namecrack @objname,
@db = @dbname output,
@object = @tabname output
if @dbname is not null
begin
/*
** 17460, "Object must be in the current database."
*/
if (@dbname != db_name())
begin
raiserror 17460
return (1)
end
end

/*
** Does the object exist?
*/
if not exists (select *
from sysobjects
where id = object_id(@objname))
begin
/*
** 17461, "Object does not exist in this database."
*/
raiserror 17461
return (1)
end

/* Get the object type */
select @type = sysstat & 15
from sysobjects
where id = object_id(@objname)
/*
** See if it's a space object.
** types are:
** 0 - trigger
** 1 - system table
** 2 - view
** 3 - user table
** 4 - sproc
** 6 - default
** 7 - rule
*/
if not exists (select *
from sysindexes
where id = object_id(@objname)
and indid < 2)
begin
if @type not in (1,2,3)
begin
/*
** 17830, "Object is stored in 'sysprocedures' and
** has no space allocated directly."
*/
raiserror 17830
return (1)
end

if @type = 2
begin
/*
** 17831, "Views don't have space allocated."
*/
raiserror 17831
return (1)
end
end

end

/*
** If @objname is null, then we want summary data.
*/
set nocount on
if @objname is null
begin

declare @slog_res_pgs int, /* number of reserved pgs. in
syslogs */
@slog_dpgs int, /* number of data pages in syslogs */
@slog_unused int, /* number of unused pages in syslogs */
@page_size int /* page size from spt_values */

select @page_size = low /* get Sun page size */
from master..spt_values
where number = 1
and type = 'E'

select "Database space allocations for " + db_name() + '.'

/* Checking to see if something besides log bit is set */
select "space where data allowed" =
ltrim (str (isnull ((sum (size) / (1048576 / @page_size)),
0), 10 ,1))
+ " MB"
from master.dbo.sysusages
where dbid = db_id()
and segmap != 4

/* Checking to see if log bit is set */
select "space where log allowed" =
ltrim (str (isnull ((sum (size) / (1048576 / @page_size)),
0), 10 ,1)) + " MB"
from master.dbo.sysusages
where dbid = db_id()
and (segmap & 4) != 0

/* Checking to see log bit not set */

select "space for data only" =
ltrim(str(isnull((sum(size) / (1048576 /
@page_size)),0),10,1)) + " MB"
from master.dbo.sysusages--, master.dbo.spt_values d
where dbid = db_id()
--and d.number = 1
--and d.type = "E"
-- having dbid = db_id()
--and d.number = 1
-- and d.type = "E"
and (segmap & 4) = 0
/*

select "space for data only" =
ltrim (str (isnull ((sum (size) / (1048576 / @page_size)),
0), 10 ,1)) + " MB"
from master.dbo.sysusages
where dbid = db_id()
and (segmap & 4) = 0

*/

/* Checking to see if only log bit is set */
/*
select "space for log only" =
ltrim (str (isnull ((sum (size) / (1048576 / @page_size)),
0), 10 ,1))
+ " MB"
from master.dbo.sysusages
where dbid = db_id()
and segmap = 4
*/
select "space for log only" =
ltrim(str(isnull((sum(size) / (1048576 /
@page_size)),0),10,1)) + " MB"
from master.dbo.sysusages --,master.dbo.spt_values d
where dbid = db_id() and segmap = 4

select "total" =
ltrim (str (isnull ((sum (size) / (1048576 / @page_size)),
0), 10 ,1))+ " MB"
from master.dbo.sysusages
where dbid = db_id()

/*
** Obtain the page count for syslogs table.
**
** The syslogs system table has only data (no index does
exist).
** Built-in functions reserved_pages and data_pages will
always
** return the same value for syslogs.
** This is due to the fact that syslogs pages are allocated
an extent
** worth at a time and all log pages in this extent are set
as in use.
** This is why we aren't able to determine the amount of
unused
** syslogs pages by simply doing reserved_pages -
data_pages.
**
** Also note that syslogs table doesn't have OAM pages.
However,
** builtin functions reserved_pages() and data_pages()
handle syslogs
** as a special case.
*/
select @slog_res_pgs = convert(numeric(20,9),
reserved_pages(db_id(), 8)),
@slog_dpgs = convert(numeric(20,9),
data_pages(db_id(), 8))

/*
** Obtain the page count for all the objects in the current
** database; except for 'syslogs' (id = 8). Store the
results
** in a temp. table (#pgcounts).
**
** Note that we first retrieve the needed information from
** sysindexes and we only then apply the OAM builtin system
** functions on that data. The reason being we want to relax
** keeping the sh_int table lock on sysindexes for the
duration
** of the command.
*/
select distinct
s.name,
s.id,
s.indid,
res_pgs = 0,
low = d.low,
dpgs = convert(numeric(20, 9), 0),
ipgs = convert(numeric(20, 9), 0),
unused = convert(numeric(20, 9), 0)
into #pgcounts
from sysindexes s, master.dbo.spt_values d
where s.id != 8
and d.number = 1
and d.type = "E"
having d.number = 1
and d.type = "E"

/* Calculate the reserved pages, data pages, index pages and
** unused pages. Note that we take care of the special case
** of indid = 1, 0 in later steps. For indid = 1 case we
need
** to get the data pages and index pages in separate steps.
*/
update #pgcounts set
res_pgs = reserved_pages(db_id(), id, indid),
ipgs = convert(numeric(20, 9), data_pages(db_id(), id,
indid)),
unused = convert(numeric(20, 9),
(reserved_pages(db_id(), id, indid)
- data_pages(db_id(), id, indid)))
where indid > 1

/* get the data pages for indid = 0 */
update #pgcounts set
res_pgs = reserved_pages(db_id(), id, indid),
dpgs = convert(numeric(20, 9), data_pages(db_id(), id,
indid)),
unused = convert(numeric(20, 9),
(reserved_pages(db_id(), id, indid)
- data_pages(db_id(), id, indid)))
where indid = 0

/* For the clustered index case calculate the data and
reserved pages
** by passing in indid of 0 to the builtins. Note, for indid
= 1
** the data pages are accounted for in ipgs.
*/
update #pgcounts set
ipgs = ipgs
+ convert(numeric(20, 9), data_pages(db_id(), id, indid)),
dpgs = dpgs
+ convert(numeric(20, 9), data_pages(db_id(), id, 0)),
res_pgs = res_pgs
+ reserved_pages(db_id(), id, 0)
+ reserved_pages(db_id(), id, indid)
where indid = 1

/* Calculate the unused count for the special case of indid
= 1 */
update #pgcounts set
unused = convert(numeric(20, 9), (res_pgs - dpgs - ipgs))
where indid = 1

/*
** Compute the summary results by adding page counts from
** individual data objects. Add to the count the count of
** pages for 'syslogs'. Convert the total pages to space
** used in Kilo bytes.
*/
/* Data only */
/*
select "Data pages:"
select distinct reserved = convert(char(15),
convert(varchar(11),
convert(numeric(11, 0), (sum(res_pgs) + @slog_res_pgs) *
(low / 1024))) + " " + "KB"),
used = convert(char(15), convert(varchar(11),
convert(numeric(11, 0), (sum(dpgs) + @slog_dpgs) *
(low / 1024))) + " " + "KB"),
index_size = convert(char(15), convert(varchar(11),
convert(numeric(11, 0), sum(ipgs) * (low / 1024)))
+ " " + "KB"),
unused = convert(char(15), convert(varchar(11),
convert(numeric(11, 0), sum(unused) * (low / 1024)))
+ " " + "KB")
-- into #fmtpgcnts
from #pgcounts

/* Log only */
--select "Log pages:" SM
--select distinct "log size" = convert(char(15),
convert(varchar(20),(@slog_dpgs * low) / 1048576) + " " +
"KB") SM
--from #pgcounts SM

*/
/* Data only */
select "Data Pages:"
select distinct reserved = convert(char(15),
convert(varchar(20),
(sum(res_pgs) * low) / 1048576) + " " + "MB"),
used = convert(char(15), convert(varchar(20),
(sum(dpgs) * low) / 1048576) + " " + "MB"),
index_size = convert(char(15), convert(varchar(20),
(sum(ipgs) * low) / 1048576) + " " + "MB"),
unused = convert(char(15), convert(varchar(20),
(sum(unused) * low) / 1048576) + " " + "MB")
from #pgcounts

/* Log only */
select "Log Pages:"
select distinct "log size" = convert(char(20),
convert(varchar(25),(@slog_dpgs * low) / 1048576) + " " +
"MB")
from #pgcounts
end
--@slog_dpgs
-- exec sp_autoformat #fmtpgcnts
-- drop table #fmtpgcnts
--end

/*
** We want a particular object.
*/
else
begin
if (@tabname = "syslogs") /* syslogs */
begin
declare @free_pages int, /* log free space in pages */
@clr_pages int, /* log space reserved for CLRs */
@total_pages int, /* total allocatable log space */
@used_pages int, /* allocated log space */
@ismixedlog int /* mixed log & data database ? */

select @ismixedlog = status2 & 32768
from master.dbo.sysdatabases where dbid = db_id()

select @clr_pages = lct_admin("reserved_for_rollbacks",
db_id())
select @free_pages = lct_admin("logsegment_freepages",
db_id())- @clr_pages

select @total_pages = sum(u.size)
from master.dbo.sysusages u
where u.segmap & 4 = 4
and u.dbid = db_id()

if(@ismixedlog = 32768)
begin
/*
** For a mixed log and data database, we cannot
** deduce the log used space from the total space
** as it is mixed with data. So we take the expensive
** way by scanning syslogs.
*/
select @used_pages = lct_admin("num_logpages", db_id())

/* Account allocation pages as used pages */
select @used_pages = @used_pages + (@total_pages / 256)
end
else
begin
/* Dedicated log database */
select @used_pages = @total_pages - @free_pages
- @clr_pages
end

select name = convert(char(15), @tabname),
total_pages = convert(char(15), @total_pages),
free_pages = convert(char(15), @free_pages),
used_pages = convert(char(15), @used_pages),
reserved_pages = convert(char(15), @clr_pages)
end
else
begin
/*
** Obtain the page count for the target object in the
current
** database and store them in the temp table #pagecounts.
**
** Note that we first retrieve the needed information from
** sysindexes and we only then apply the OAM builtin system
** functions on that data. The reason being we want to relax
** keeping the sh_int table lock on sysindexes for the
duration
** of the command.
*/
select name = o.name,
tabid = i.id,
iname = i.name,
indid = i.indid,
low = d.low,
rowtotal = 0,
reserved = convert(numeric(20, 9), 0),
data = convert(numeric(20, 9), 0),
index_size = convert(numeric(20, 9), 0),
unused = convert(numeric(20, 9), 0)
into #pagecounts
from sysobjects o, sysindexes i, master.dbo.spt_values d
where i.id = object_id(@objname)
and o.id = i.id
and d.number = 1
and d.type = "E"

/* perform the row counts */
update #pagecounts
set rowtotal = row_count(db_id(), tabid)
where indid <= 1

/* calculate the counts for indid > 1
** case of indid = 1, 0 are special cases done later
*/
update #pagecounts set
reserved = convert(numeric(20, 9),
reserved_pages(db_id(), tabid, indid)),
index_size = convert(numeric(20, 9),
data_pages(db_id(), tabid, indid)),
unused = convert(numeric(20, 9),
((reserved_pages(db_id(), tabid, indid) -
(data_pages(db_id(), tabid, indid)))))
where indid > 1

/* calculate for case where indid = 0 */
update #pagecounts set
reserved = convert(numeric(20, 9),
reserved_pages(db_id(), tabid, indid)),
data = convert(numeric(20, 9),
data_pages(db_id(), tabid, indid)),
unused = convert(numeric(20, 9),
((reserved_pages(db_id(), tabid, indid) -
(data_pages(db_id(), tabid, indid)))))
where indid = 0

/* handle the case where indid = 1, since we need
** to take care of the data and index pages.
*/
update #pagecounts set
reserved = convert(numeric(20, 9),
reserved_pages(db_id(), tabid, 0))
+ convert(numeric(20, 9),
reserved_pages(db_id(), tabid, indid)),
index_size = convert(numeric(20, 9),
data_pages(db_id(), tabid, indid)),
data = convert(numeric(20, 9),
data_pages(db_id(), tabid, 0))
where indid = 1

/* calculate the unused count for indid = 1 case.*/
update #pagecounts set
unused = convert(numeric(20, 9),
reserved - data - index_size)
where indid = 1

if (@list_indices = 1)
begin
select index_name = iname,
size = convert(char(10), convert(varchar(11),
convert(numeric(11, 0),
index_size / 1024 *
low)) + " " + "MB"),
reserved = convert(char(10),
convert(varchar(11),
convert(numeric(11, 0),
reserved / 1024 *
low)) + " " + "MB"),
unused = convert(char(10), convert(varchar(11),
convert(numeric(11, 0), unused / 1024 *
low)) + " " + "MB")
into #formatpgcounts
from #pagecounts
where indid > 0

exec sp_autoformat #formatpgcounts
drop table #formatpgcounts
end

select distinct name,
rowtotal = convert(char(11), sum(rowtotal)),
reserved = convert(char(15), convert(varchar(11),
convert(numeric(11, 0), sum(reserved) *
(low / 1024))) + " " + "MB"),
data = convert(char(15), convert(varchar(11),
convert(numeric(11, 0), sum(data) * (low / 1024)))
+ " " + "MB"),
index_size = convert(char(15), convert(varchar(11),
convert(numeric(11, 0), sum(index_size) *
(low / 1024))) + " " + "MB"),
unused = convert(char(15), convert(varchar(11),
convert(numeric(11, 0), sum(unused) *
(low / 1024))) + " " + "MB")
into #fmtpgcounts
from #pagecounts

exec sp_autoformat #fmtpgcounts
drop table #fmtpgcounts
end
end
return (0)
go
EXEC sp_procxmode 'dbo.sp__spaceused','anymode'
go

Any help would be appreciated

- Regards
DBA

[Mark A. Parsons]

What exactly is your question/issue/problem?

The difference in 0 MB and 0.02 MB ?

used_pages = 241 ?

something else?

DBA wrote:
> I have this SP(sp__spaceused) I am trying to fix the o/p
> where it says Log Pages: log size 0 MB
>
> But when we view it using DBartisan is shows 0.02 MB for the
> DB used and even when you run the sp_helpsegment logsegment
> it shows used_pages as 241 it’s very similar to ase 15
> sp_spaceused but had to be modified for this shop .
>
> create procedure sp__spaceused
> @objname varchar(317) = null, /* the object we want size
> on */
> @list_indices int = 0 /* don't sum all indices, list each
> */

... snip ...

[Simon Ogden]

Hi,

The simplest way to get your precision back is to change your divisor for
the Mb to a numeric (9,2)

So - change:-

/* Log only */
select "Log Pages:"
select distinct "log size" = convert(char(20),
convert(varchar(25),(@slog_dpgs * low) / 1048576) + " " +
"MB")
from #pgcounts

To:-

/* Log only */
select "Log Pages:"
select distinct "log size" = convert(char(20),

convert(varchar(25),(@slog_dpgs * low) / 1048576.00) + " " +

"MB")
from #pgcounts
end

Hope this helps.

Simon

<DBA> wrote in message news:46fab738.55f...@sybase.com...

[DBA]

thanks Simon , that's what i was looking for Appreciated
-DBA

[DBA]

The o/p that i am getting was - But when we use DBArtisan to
view the space for the logsize it usually shows 0.02 MB so
the SP was not able to show the o/p as required for the Log
Space . But i did see one more reply to my query where after
changing the divisor to 1048576.00 it did show the o/p what
artisan or sp_helpsegment shows .

*********************

--------------------------------------------------------------
Database space allocations for refd1_supplier.

space where data allowed
------------------------
40.0 MB

space where log allowed
-----------------------
30.0 MB

space for data only
-------------------
40.0 MB

space for log only
------------------
30.0 MB

total
-------------
70.0 MB

-----------
Data Pages:

reserved used index_size unused
--------------- --------------- --------------- ---------------
10 MB
6.7324218750000 2.4707031250000 1.7402343750000

----------
Log Pages:

log size
-------------------
0 MB
(return status = 0)
*********************************

After the change 1048576.00
*********************************

--------------------------------------------------------------
Database space allocations for refd1_reference_cur.

space where data allowed
------------------------
70.0 MB

space where log allowed
-----------------------
30.0 MB

space for data only
-------------------
70.0 MB

space for log only
------------------
30.0 MB

total
-------------
100.0 MB

-----------
Data Pages:

reserved used index_size unused
--------------- --------------- --------------- ---------------
5 MB
2.8085937500000 0.3105468750000 1.9765625000000

----------
Log Pages:

log size
--------------------
0.0156250000 MB
(return status = 0)
***********************

Regards

DBA

[DBA]

Mark,

it now giving me Arithmetic overflow accured error message
and is not able to calculte the datapages for huge DB's
after making the 1048576.00 change in the SP

******************

--------------------------------------------------------------
Database space allocations for astd1_asset.

space where data allowed
------------------------

22449.0 MB

space where log allowed
-----------------------

600.0 MB

space for data only
-------------------

22449.0 MB

space for log only
------------------

600.0 MB

total
-------------
23049.0 MB

-----------
Data Pages:

reserved used index_size unused
-------------------- --------------- --------------- ---------------
(return status = -6)
************************************************

[DBA]

it now giving me Arithmetic overflow accured error message
and is not able to calculte the datapages for huge DB's

******************

--------------------------------------------------------------
Database space allocations for astd1_asset.

space where data allowed
------------------------
22449.0 MB

space where log allowed
-----------------------
600.0 MB

space for data only
-------------------
22449.0 MB

space for log only
------------------
600.0 MB

total
-------------
23049.0 MB

-----------
Data Pages:

reserved used index_size unused
-------------------- --------------- --------------- ---------------
(return status = -6)
************************************************

> Hi,

[Mark A. Parsons]

You haven't provided the actual SQL that's generating the overflow so I'll hazard a guess ...

You're using integer math while multiplying the number of pages by 1048576 and then dividing by the dataserver page size
(eg, 2048).

Try dividing 1048576 by the dataserver page size and then multiply by the number of pages. Objective is to keep from
going above 2billion with your integer math, like such:

(1048576 / dataserver_page_size) * #_of_pages

[DBA]

Mark,

Here is the SQL that failes

**************************

select "Data Pages:"
select distinct reserved = convert(char(15),
convert(varchar(20),
(sum(res_pgs) * low) / 1048576) + " " + "MB"),
used = convert(char(15), convert(varchar(20),
(sum(dpgs) * low) / 1048576) + " " + "MB"),
index_size = convert(char(15), convert(varchar(20),
(sum(ipgs) * low) / 1048576) + " " + "MB"),
unused = convert(char(15), convert(varchar(20),
(sum(unused) * low) / 1048576) + " " + "MB")
from #pgcounts

/* Log only */
select "Log Pages:"
select distinct "log size" = convert(char(20),

convert(varchar(25),(@slog_dpgs * low) / 1048576.00) + " " +
"MB")
from #pgcounts

*****************************************************

> You haven't provided the actual SQL that's generating the
> overflow so I'll hazard a guess ...
>
> You're using integer math while multiplying the number of
> pages by 1048576 and then dividing by the dataserver page
> size (eg, 2048).
>
> Try dividing 1048576 by the dataserver page size and then
> multiply by the number of pages. Objective is to keep
> from going above 2billion with your integer math, like
> such:
>
> (1048576 / dataserver_page_size) * #_of_pages
>
> DBA wrote:
> > Mark,
> >
> > it now giving me Arithmetic overflow accured error
> > message and is not able to calculte the datapages for
> > huge DB's after making the 1048576.00 change in the SP
> >
> > ******************
> >
> >
> ----------------------------------------------------------

> > ---- Database space allocations for astd1_asset.

[Mark A. Parsons]

So now you provide a query based solely on the contents of a temp table (#pgcount) but provide no information on how
#pgcounts is populated or any idea of what's in the various columns of said table.

I'd suggest you take a gander at the sp_helpdb and sp_spaceused stored procs to get a better idea of how to calculate
database and object space usage numbers. Those procs (obviously ?) don't have problems with space calculation numbers
so you may save yourself a lot of headaches by copying some code that already works.

[DBA]

Thanks Mark