bejhan
asked on
Sybase database sizes procedure
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 (https://www.experts-exchange.com/questions/27409252/Sybase-device-sizes-procedure.html?anchorAnswerId=37042158#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.
Could someone verify that this procedure is valid? And whether it should be written more efficiently?
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.8274751> 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.053787Could 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
What do you want this procedure to report on for any database with data and log mixed?
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.
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
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.
ASKER
Wow the result of the new and old procedures is quite different, good thing I posted this question :P.
Old
New
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.
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.090145New
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.153846As for the mixed situation, what was the old procedure doing with mixed data and log? I want to try to keep the output similar.
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.
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.
ASKER
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)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).
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. :)
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. :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Heh, typo in documentation, replace 2049 with 2048. :)
ASKER
Thanks so much for your help!
ASKER
Shoot, I just noticed a problem:
Total used should be 203 here.
Any idea why this is happening?
Type Used Allotted PercentFull
----- --------------------------- --------------------------- ---------------------------
data 202.000000 300.000000 67.333333
log 1.000000 350.000000 0.285714
total 202.000000 650.000000 31.076923Total used should be 203 here.
Any idea why this is happening?
ASKER
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
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.