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), 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
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE