A fast method of getting space utilization: sp__quickdbspace for SAP Sybase ASE

The sp__quickdbspace procedure relies on accurate metrics provided by free space accounting and/or dbcc checkalloc/tablealloc. It shouldn’t be relied upon on for exact space usage.

Much of the code comes from sp_helpdb from Sybase. er.. SAP.

sp__quickdbspace output:

1>  sp__quickdbspace mydb
2> go
 DBName               data size MB       data free MB       log free MB
 -------------------- ------------------ ------------------ ------------------
 mydb                         5025662.00          703975.00          186260.00
(return status = 0)
1>

sp__quickdbspace code:

if exists (select 1 from sysobjects where name = "sp__quickdbspace")
  drop procedure sp__quickdbspace
go

create procedure sp__quickdbspace 
@dbname varchar(255) = NULL
as
declare @numpgsmb float	/* Number of Pages per Megabyte */
declare @sptlang int
declare @log_free bigint

set nocount on

if @dbname = null
  select @dbname = db_name()

select @numpgsmb = (1048576. / v.low)
from master.dbo.spt_values v
where v.number = 1
and v.type = "E"	

select @sptlang = @@langid

if @@langid != 0
begin
  if not exists (select * from master.dbo.sysmessages where error between 17050 and 17069 and langid = @@langid)
    select @sptlang = 0
  else
    if not exists (select * from master.dbo.sysmessages where error between 17110 and 17119 and langid = @@langid)
      select @sptlang = 0
end

select 
  device_fragments = v.name,
  size = (size / @numpgsmb),
  usage = convert(char(30), m.description),
  created = convert(char(25), u.crdate, 100),
  case
    when u.segmap = 4 then null
    else (convert(bigint, curunreservedpgs(d.dbid, u.lstart, u.unreservedpgs)) / @numpgsmb)
  end "free_mb"
  into #device_usage
from master.dbo.sysdatabases d,
  master.dbo.sysusages u,
  master.dbo.sysdevices v,
  master.dbo.spt_values b,
  master.dbo.sysmessages m
where d.dbid = u.dbid
  and u.vdevno = v.vdevno
  and ((v.status & 2 = 2)  or (v.status2 & 8 = 8))
  and d.name = @dbname
  and b.type = 'S'
  and u.segmap & 7 = b.number
  and b.msgnum = m.error
  and isnull(m.langid, 0) = @sptlang
  
	if exists (select *
	    from master.dbo.sysdatabases d, master.dbo.sysusages u
		where d.name like @dbname
			and d.dbid = u.dbid
			and u.segmap = 4
			and (d.status & ( 32 + 64 + 128 + 256) = 0)
			and (d.status2 & 64) = 0)
	begin
		 select @log_free = (lct_admin("logsegment_freepages", db_id(@dbname) ) - lct_admin("reserved_for_rollbacks", db_id(@dbname))) / @numpgsmb
	end
    
    select convert(varchar(20), @dbname) as "DBName", convert(numeric(15,2), sum(size)) as "data size MB", convert(numeric(15,2), sum(free_mb)) as "data free MB", convert(numeric(15,2), @log_free) as "log free MB" from #device_usage
    
drop table #device_usage
go
Share Button

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.