?
Solved

Sybase database sizes procedure

Posted on 2011-10-28
13
Medium Priority
?
695 Views
Last Modified: 2012-05-12
I am working with two stored procedures created by someone who is no longer at my firm. I found one procedure to be very inaccurate (http://www.experts-exchange.com/Database/Sybase/Q_27409252.html#a37042158) so I thought I should evaluate the accuracy and efficiency of the other one, though it appears to be working correctly.

The procedure takes two arguments:
1) dbname - The name of the database of whose size to return.
2) type - The type of sizes to return. If 'all' is specified then data, log, and total sizes are returned, otherwise just data is returned.
I'm not sure why they didn't just use a boolean for this since there are only two possibilities.

1> sp_dbcapacity 'inhouse_config'
2> go
 Type  Used                        Allotted                    PercentFull
 ----- --------------------------- --------------------------- ---------------------------
 data                   801.929696                 1200.000000                   66.827475

Open in new window


1>  sp_dbcapacity 'inhouse_config', 'all'
2> go
 Type  Used                        Allotted                    PercentFull
 ----- --------------------------- --------------------------- ---------------------------
 data                   801.929696                 1200.000000                   66.827475
 log                      5.468760                 1400.000000                    0.390626
 total                  807.398456                 2600.000000                   31.053787

Open in new window


Could someone verify that this procedure is valid? And whether it should be written more efficiently?
/*
* a.low = bytes per page = 2048
* u.size = size (in pages) of the database
* u.lstart = starting page of the database
*/

create proc sp_dbcapacity @dbname varchar(30)='null', @type varchar(30)='null' as
declare @dbid int, @pagekb int

select @pagekb = (low / 1024) from master.dbo.spt_values where number = 1 and type = 'E'
select @dbid=dbid from master..sysdatabases where name=@dbname

/* Create the temporary table first, so it knows what data types to expect from the inserts. */
create table #size
( Type VARCHAR(5), Used FLOAT, Allotted FLOAT, PercentFull FLOAT )

insert #size select "data" Type, sum((u.size/(1048576/a.low))-@pagekb*
 (curunreservedpgs(@dbid,u.lstart,u.unreservedpgs)/1024.0)) Used,
 sum((u.size/(1048576/a.low))) Allotted, 
 (100*(sum(((u.size)/(1048576/a.low))-@pagekb*
 (curunreservedpgs(@dbid,u.lstart,u.unreservedpgs)/1024.0)))/
 sum(((u.size)/(1048576/a.low)))) PercentFull from 
 master.dbo.sysdatabases d, master.dbo.sysusages u, master.dbo.sysdevices v, 
 master.dbo.spt_values a, master.dbo.spt_values b, master.dbo.sysmessages m 
 where d.dbid=u.dbid and v.low<=u.size+vstart and v.high>=u.size+vstart-1 
 and v.status&2=2 and d.dbid=@dbid and a.type="E" and 
 a.number=1 and b.type="S" and u.segmap=3 and u.segmap&7=b.number and
 b.msgnum=m.error
 
if (@type="all")
begin
 insert #size select "log" Type,sum((u.size/(1048576/a.low))-@pagekb*
  (curunreservedpgs(@dbid,u.lstart,u.unreservedpgs)/1024.0)) Used, 
  sum ((u.size/(1048576/a.low))) Allotted, 
  (100*(sum((u.size/(1048576/a.low))-@pagekb*
  (curunreservedpgs(@dbid,u.lstart,u.unreservedpgs)/1024.0)))/
  sum((u.size/(1048576/a.low)))) PercentFull from 
  master.dbo.sysdatabases d, master.dbo.sysusages u, master.dbo.sysdevices v, 
  master.dbo.spt_values a, master.dbo.spt_values b, master.dbo.sysmessages m 
  where d.dbid=u.dbid and v.low<=u.size+vstart and v.high>=u.size+vstart-1 
  and v.status&2=2 and d.dbid=@dbid and a.type="E" and 
  a.number=1 and b.type="S" and u.segmap=4 and u.segmap&7=b.number and 
  b.msgnum=m.error
  
 insert #size select "total" Type, sum((u.size/(1048576/a.low))-@pagekb*
  (curunreservedpgs(@dbid,u.lstart,u.unreservedpgs)/1024.0)) Used, 
  sum ((u.size/(1048576/a.low))) Allotted, 
  (100*(sum((u.size/(1048576/a.low))-@pagekb*
  (curunreservedpgs(@dbid,u.lstart,u.unreservedpgs)/1024.0)))/
  sum((u.size/(1048576/a.low)))) PercentFull from 
  master.dbo.sysdatabases d, master.dbo.sysusages u, master.dbo.sysdevices v,
  master.dbo.spt_values a, master.dbo.spt_values b, master.dbo.sysmessages m
  where d.dbid=u.dbid and v.low<=u.size+vstart and v.high>=u.size+vstart-1
  and v.status&2=2 and d.dbid=@dbid and a.type="E" and
  a.number=1 and b.type="S" and u.segmap&7=b.number and b.msgnum=m.error
end

select * from #size
go

Open in new window

0
Comment
Question by:bejhan
  • 8
  • 5
13 Comments
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 37049394
This is still using pre-ASE 15.0 code and will no longer work properly in ASE 15.0+. The clue is joining sysusages to sysdevices based on vstart rather than on vdevno. Likewise it makes the same assumption as your other proc that pages only ever hold 2Kb... which, interestingly, was not true even in ASE 12.5.

Basically the code is wrong and has always been wrong, but in ASE 15.0+ it is even more wrong than it was before. :)

The system functions it calls aren't guaranteed to be correct either, but perhaps you're ok with just an approximate figure that should most of the time be moderately accurate?

Of more concern is the suggestion that your ASE has not been properly/fully upgraded to ASE 15.0. We've seen at least two in-house scripts you apparently depend on that just plain won't work properly in ASE 15.0, and that raises some questions in my mind as to how the rest of the upgrade was carried out. For example anything with a GROUP BY or a cursor may not return the same results pre-15.0 vs 15.0 and above.

I'll have a go at the code within 24 hours; I suspect it can be both simplified and made more efficient. :) It does some very strange things like looking up system tables when it doesn't need to, creating the table first when it doesn't need to, etc etc etc.
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 37052766
What do you want this procedure to report on for any database with data and log mixed?
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 37052856
I've got a version for you here that only reports on data and log where data and log are fully separated.

I don't have a complicated set of test databases here so it's possibly some of the original very weird-looking code is actually necessary... :)

The code below drops and recreates it each time you run it. Note I've called this version sp_dbacapacity2 just so we don't clobber your current one.    

It will only work in ASE 15.0 and above.
use sybsystemprocs
go

if exists (select 1 from sysobjects where type = "P" and name="sp_dbacapacity2")
   drop proc sp_dbacapacity2
go

create 	proc sp_dbacapacity2
	@dbname varchar(30)='null'
,	@type varchar(30)='null'
as

select	Type =	convert(varchar(5), "data")
,	Unused = convert(numeric(15,6),	isnull(sum(curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs)),0)/(@@pagesize/8))
,	Allotted = convert(numeric(15,6), isnull(sum(1.0*u.size)/(@@pagesize/8), 1))
into	#capacity
from	master..sysusages u
where	u.dbid = db_id(@dbname)             
and	u.segmap & 4 = 0 and u.segmap > 0                     -- anything but log

if (lower(@type) = "all")
begin
insert  #capacity
select	convert(varchar(5), "log")
,	convert(numeric(15,6), isnull(sum(curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs)),0)/(@@pagesize/8))
,	convert(numeric(15,6), isnull(sum(1.0*u.size)/(@@pagesize/8), 0))
from	master..sysusages u
where	u.dbid = db_id(@dbname)
and	u.segmap = 4 and u.segmap > 0                          -- only log

insert  #capacity
select	convert(varchar(5), "mixed")
,	convert(numeric(15,6), isnull(sum(curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs)),0)/(@@pagesize/8))
,	convert(numeric(15,6), isnull(sum(1.0*u.size)/(@@pagesize/8), 0))
from	master..sysusages u
where	u.dbid = db_id(@dbname)
and	u.segmap & 4 = 4 and u.segmap <> 4 and u.segmap > 0    -- data & log mixed

insert  #capacity
select	convert(varchar(5), "total")
,	convert(numeric(15,6), isnull(sum(curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs)),0)/(@@pagesize/8))
,	convert(numeric(15,6), isnull(sum(1.0*u.size)/(@@pagesize/8), 0))
from	master..sysusages u
where	u.dbid = db_id(@dbname)
and	u.segmap > 0
end

select	Type
,	Used = Allotted - Unused
,	Allotted
,	PercentFull = case when Allotted > 0 then convert(numeric(9,6), 100.0 * (Allotted - Unused) /  Allotted)
			   else 0.000000
		      end
from	#capacity
go

Open in new window

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 37052862
If you'd like to ignore the situation of data & log being mixed, and just report on data and log separately (which will sum to more than the size of the database if you don't treat mixed separately) let me know, that's pretty easy to change.
0
 
LVL 1

Author Comment

by:bejhan
ID: 37063936
Wow the result of the new and old procedures is quite different, good thing I posted this question :P.

Old
1> sp_dbcapacity 'inhouse_config', 'all'
2> go
 Type  Used                        Allotted                    PercentFull
 ----- --------------------------- --------------------------- ---------------------------
 data                   802.875008                 1200.000000                   66.906251
 log                      5.468760                 1400.000000                    0.390626
 total                  808.343768                 2600.000000                   31.090145

Open in new window


New
1> sp_dbacapacity2 'inhouse_config', 'all'
2> go
 Type  Used                Allotted           PercentFull
 ----- ------------------- ------------------ ------------
 data           402.000000         600.000000    67.000000
 log              3.000000         700.000000     0.428571
 mixed            0.000000           0.000000     0.000000
 total          405.000000        1300.000000    31.153846

Open in new window


As for the mixed situation, what was the old procedure doing with mixed data and log? I want to try to keep the output similar.

0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 37064007
Hmm, the page size calculations may not be right since it looks like your numbers have halved. Please post the output of

   select @@pagesize
   exec sp_helpdb inhouse_config

because I'm not fully convinced my code is correct yet. (It worked fine on my 4K page server but maybe I should create a couple of others to test this.)

The old code looks like it counted "mixed" to both data and to log, but didn't sum them in "total", so you could get a situation where it claimed there was data=100, log=100, total=100.

I have to admit there are a couple of things in the old code I simply couldn't see any need for at all, but perhaps they were actually there for a good reason. As I said, the new code works fine in my test server but maybe I'm not testing enough boundary cases.
0
 
LVL 1

Author Comment

by:bejhan
ID: 37064914
1> select @@pagesize
2> go

 -----------
        2048

(1 row affected)
1> exec sp_helpdb inhouse_config
2> go
 name           db_size       owner dbid created      durability status
 -------------- ------------- ----- ---- ------------ ---------- ---------------------------------------------------------------
 inhouse_config      650.0 MB sa       6 May 09, 2011 full       select into/bulkcopy/pllsort, trunc log on chkpt, auto identity

(1 row affected)
 device_fragments               size          usage                created                   free kbytes
 ------------------------------ ------------- -------------------- ------------------------- ----------------
 db_device2                          200.0 MB data only            Oct 18 2011  4:38PM                  42268
 db_device2                          100.0 MB log only             Oct 18 2011  4:38PM       not applicable
 log_device2                         250.0 MB log only             Oct 18 2011  4:38PM       not applicable
 log_device2                         100.0 MB data only            Oct 18 2011  4:38PM                  59396

 --------------------------------------------------------------
 log only free kbytes = 356998
(return status = 0)

Open in new window


I think instead of having mixed returned I would like to have data=100, log=100, total=100. Most applications only use the data sizes so this would keep their results the same and for the application showing totals, this would indicate that data and log are shared (since total=200 would technically be wrong since they only have 100 to share).
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 37067629
Interesting, for your database both the old and the new code are wrong. :)

sp_helpdb reports it is only 650Mb in size. My code says 1300Mb and the old code says 2600Mb! Let me play with numbers and I'll see what I need to change.

Easy enough to collapse "mixed" back into data and log per the old way. Let me get back to you. :)
0
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 2000 total points
ID: 37089509
Ok. I saw what I was doing wrong. This will now work with any page size. (My main mistake was using @@pagesize rather than @@maxpagesize, a silly error I should have known better. @@pagesize is always 2048 regardless of actual page size.)

I have cleaned up the code, made it a bit easier to understand and added enough documentation so the next person looking at this in five years will be able to figure it out. :)

I have gotten rid of "mixed" and now mixed segments will be double-counted to both data and log. Also if there are any completely unused segments (for example the first fragment of tempdb on master is frequently updated to be unused), they won't count at all. Lastly I've made some very minor efficiency changes - performance isn't a key for something that works on as few rows as this does, but why not make it as clean as possible eh?

Try this version out. Don't forget to rename to sp_dbacapacity when you're finally happy with it. I think you'll agree this is easier to understand than your original version!! :)
use sybsystemprocs
go

if exists (select 1 from sysobjects where type = "P" and name="sp_dbacapacity2")
   drop proc sp_dbacapacity2
go

create 	proc sp_dbacapacity2
	@dbname varchar(30)='null'
,	@type varchar(30)='null'
as

declare @Mb int

select	@Mb=1024*1024
-- number of pages * @@maxpagesize / @Mb gives us size in Mb
-- NOTE: we must use @@maxpagesize, not @@pagesize which is always 2049 regardless of actual page size

select	Type =	convert(varchar(5), "data")
,	Unused = convert(numeric(15,6),	isnull(sum(curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs)),0)*@@maxpagesize/@Mb)
,	Allotted = convert(numeric(15,6), isnull(1.0*sum(u.size)*@@maxpagesize/@Mb, 1))
into	#capacity
from	master..sysusages u
where	u.dbid = db_id(@dbname)             
and	u.segmap <> 4 and u.segmap > 0                     -- anything but log only; this includes mixed data & log

if (lower(@type) = "all")
begin
insert  #capacity
select	convert(varchar(5), "log")
,	convert(numeric(15,6), isnull(sum(curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs)),0)*@@maxpagesize/@Mb)
,	convert(numeric(15,6), isnull(1.0*sum(u.size)*@@maxpagesize/@Mb, 0))
from	master..sysusages u
where	u.dbid = db_id(@dbname)
and	u.segmap & 4 = 4 and u.segmap > 0                          --  log; this includes mixed data & log

insert  #capacity
select	convert(varchar(5), "total")
,	convert(numeric(15,6), isnull(sum(curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs)),0)*@@maxpagesize/@Mb)
,	convert(numeric(15,6), isnull(1.0*sum(u.size)*@@maxpagesize/@Mb, 0))
from	master..sysusages u
where	u.dbid = db_id(@dbname)
and	u.segmap > 0
end

select	Type
,	Used = Allotted - Unused
,	Allotted
,	PercentFull = case when Allotted > 0 then convert(numeric(9,6), 100.0 * (Allotted - Unused) /  Allotted)
			   else 0.000000
		      end
from	#capacity
go

Open in new window

0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 37089511
Heh, typo in documentation, replace 2049 with 2048. :)
0
 
LVL 1

Author Closing Comment

by:bejhan
ID: 37096084
Thanks so much for your help!
0
 
LVL 1

Author Comment

by:bejhan
ID: 37096374
Shoot, I just noticed a problem:

 Type  Used                        Allotted                    PercentFull
 ----- --------------------------- --------------------------- ---------------------------
 data                   202.000000                  300.000000                   67.333333
 log                      1.000000                  350.000000                    0.285714
 total                  202.000000                  650.000000                   31.076923

Open in new window


Total used should be 203 here.

Any idea why this is happening?
0
 
LVL 1

Author Comment

by:bejhan
ID: 37108228
Here is the final procedure, with the above problem fixed.
use sybsystemprocs
go

if exists (select 1 from sysobjects where type = "P" and name="sp_dbacapacity2")
   drop proc sp_dbacapacity2
go

create 	proc sp_dbacapacity2
	@dbname varchar(30)='null'
,	@type varchar(30)='null'
as

declare @Mb int

select	@Mb=1024*1024
-- number of pages * @@maxpagesize / @Mb gives us size in Mb
-- NOTE: we must use @@maxpagesize, not @@pagesize which is always 2049 regardless of actual page size

select	Type =	convert(varchar(5), "data")
,	Unused = convert(numeric(15,6),	1.0*isnull(sum(curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs)),0)*@@maxpagesize/@Mb)
,	Allotted = convert(numeric(15,6), 1.0*isnull(sum(u.size)*@@maxpagesize/@Mb, 1))
into	#capacity
from	master..sysusages u
where	u.dbid = db_id(@dbname)             
and	u.segmap <> 4 and u.segmap > 0                     -- anything but log only; this includes mixed data & log

if (lower(@type) = "all")
begin
insert  #capacity
select	convert(varchar(5), "log")
,	convert(numeric(15,6), 1.0*isnull(sum(curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs)),0)*@@maxpagesize/@Mb)
,	convert(numeric(15,6), 1.0*isnull(sum(u.size)*@@maxpagesize/@Mb, 0))
from	master..sysusages u
where	u.dbid = db_id(@dbname)
and	u.segmap & 4 = 4 and u.segmap > 0                          --  log; this includes mixed data & log

insert  #capacity
select	convert(varchar(5), "total")
,	convert(numeric(15,6), 1.0*isnull(sum(curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs)),0)*@@maxpagesize/@Mb)
,	convert(numeric(15,6), 1.0*isnull(sum(u.size)*@@maxpagesize/@Mb, 0))
from	master..sysusages u
where	u.dbid = db_id(@dbname)
and	u.segmap > 0
end

select	Type
,	Used = Allotted - Unused
,	Allotted
,	PercentFull = case when Allotted > 0 then convert(numeric(9,6), 100.0 * (Allotted - Unused) /  Allotted)
			   else 0.000000
		      end
from	#capacity
go

Open in new window

0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
I came across an unsolved Outlook issue and here is my solution.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question