Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.
1> sp_dbcapacity 'inhouse_config'
2> go
Type Used Allotted PercentFull
----- --------------------------- --------------------------- ---------------------------
data 801.929696 1200.000000 66.827475
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
/*
* 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
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
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
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
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)
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
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
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
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.