To get Sybase Database Usage details

Database is combination of data segment and log segment. It is DBA responsibility to monitor database on daily basis. We can database total space, used space and free space available in a database by running sp_helpsegment stored procedure.

Examples:

1. To get data segment usage and free details.

sp_helpsegment 'default'
go

2. To get log segment usage and free details.

sp_helpsegment 'logsegment'
go

3. Query to get Sybase Database Usage details:

use r2schools
go

declare @db__id int
declare @dbname varchar(30)

select @db__id =db_id()
select @dbname = db_name(@db__id)

print "Current database:%1!", @dbname
declare @showdev bit
declare @alloc numeric

select @alloc = low from master.dbo.spt_values
where type='E' and number=1

select s.name Segment,
convert(numeric,sum((size*@alloc)/1048576)) 'size in MB',
convert(numeric,sum((size*@alloc)/1048576))-
convert(numeric,sum((curunreservedpgs(@db__id,lstart, unreservedpgs)* @alloc)/1048576)) 'Used in MB',
convert(numeric,sum((curunreservedpgs(@db__id,lstart, unreservedpgs)* @alloc)/1048576)) 'Free in MB' 

from master..sysusages u, syssegments s
where u.dbid=@db__id and (segmap & power(2, segment))=power(2,segment)
group by s.name