We help IT Professionals succeed at work.
Get Started

Sybase database sizes procedure, mostly working but found a weird result

bejhan
bejhan asked
on
866 Views
Last Modified: 2012-05-12
I asked for help with my Sybase procedure with returns database sizes at question: https://www.experts-exchange.com/Database/Sybase/Q_27421159.html?cid=1575

The newly written procedure works greats but I hastily closed the question before I noticed a problem with the result.

 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?
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

Comment
Watch Question
Principal Consultant
CERTIFIED EXPERT
Most Valuable Expert 2012
Commented:
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answer
Why Experts Exchange?

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.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE