SAP Sybase ASE 15.7 sp_dbsummary Database Summary



sp_dbsummary Database Summary

This non-SAP stored procedure provides a summary of sybase databases on an ASE dataserver.

This procedure answers questions such as:

What is the percentage data segment free for the database?
What is the total amount of data segment free for the database in MB?
What is the total of the log segments for the database?

Sample Output

1> sp_dbsummary
 
Mixed data and log databases:
                                                                                                                                                                                                                                                                                                                                                                                                            
 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
 db_name=master:  total_size_mb=26MB total_mixed_free_kb=13900KB total_mixed_percent_free=52%                                                                                                                                                                                                                                                                                                               
 db_name=tempdb:  total_size_mb=106MB total_mixed_free_kb=104520KB total_mixed_percent_free=96%                                                                                                                                                                                                                                                                                                             
 db_name=model:  total_size_mb=6MB total_mixed_free_kb=2712KB total_mixed_percent_free=44%                                                                                                                                                                                                                                                                                                                  
 db_name=test_tempdb:  total_size_mb=110MB total_mixed_free_kb=108792KB total_mixed_percent_free=96%                                                                                                                                                                                                                                                                                                        
 db_name=TEMPDB_2:  total_size_mb=50MB total_mixed_free_kb=47592KB total_mixed_percent_free=92%                                                                                                                                                                                                                                                                                                             
 db_name=sybsystemdb:  total_size_mb=12MB total_mixed_free_kb=8608KB total_mixed_percent_free=70%                                                                                                                                                                                                                                                                                                           
 db_name=sybsystemprocs:  total_size_mb=172MB total_mixed_free_kb=12860KB total_mixed_percent_free=7%                                                                                                                                                                                                                                                                                                       
 db_name=sybmgmtdb:  total_size_mb=75MB total_mixed_free_kb=50912KB total_mixed_percent_free=66%                                                                                                                                                                                                                                                                                                            
 
Separate data and log databases:
================================
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
 db_name=MOVE_TEST:  total_size_mb=10MB total_data_mb=5MB total_log_mb=5MB total_data_free=1724KB total_log_free=5068KB total_data_percent_free=33% total_log_percent_free=98%                                                                                                                                                                                                                                                                                                                                                    
 db_name=altest_new:  total_size_mb=6MB total_data_mb=4MB total_log_mb=2MB total_data_free=672KB total_log_free=2000KB total_data_percent_free=16% total_log_percent_free=97%                                                                                                                                                                                                                                                                                                                                                     
 db_name=primary_test:  total_size_mb=1000MB total_data_mb=500MB total_log_mb=500MB total_data_free=444144KB total_log_free=388804KB total_data_percent_free=86% total_log_percent_free=75%                                                                                                                                                                                                                                                                                                                                       
 db_name=pushpullsigns:  total_size_mb=1281MB total_data_mb=545MB total_log_mb=736MB total_data_free=316732KB total_log_free=750692KB total_data_percent_free=56% total_log_percent_free=99%                                                                                                                                                                                                                                                                                                                                      
 db_name=raw_test:  total_size_mb=1000MB total_data_mb=500MB total_log_mb=500MB total_data_free=396144KB total_log_free=509968KB total_data_percent_free=77% total_log_percent_free=99%                                                                                                                                                                                                                                                                                                                                           
 db_name=second_disk_fs:  total_size_mb=1000MB total_data_mb=500MB total_log_mb=500MB total_data_free=396144KB total_log_free=267204KB total_data_percent_free=77% total_log_percent_free=52%                                                                                                                                                                                                                                                                                                                                     

Summary of Data Segments Free in MB
----------------------------------
                                                                                                                                                                                                                                                                                                    
 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
 db_name=MOVE_TEST:  total_data_free=1MB total_data_percent_free=33%                                                                                                                                                                                                                                
 db_name=altest_new:  total_data_free=0MB total_data_percent_free=16%                                                                                                                                                                                                                               
 db_name=primary_test:  total_data_free=433MB total_data_percent_free=86%                                                                                                                                                                                                                           
 db_name=pushpullsigns:  total_data_free=309MB total_data_percent_free=56%                                                                                                                                                                                                                          
 db_name=raw_test:  total_data_free=386MB total_data_percent_free=77%                                                                                                                                                                                                                               
 db_name=second_disk_fs:  total_data_free=386MB total_data_percent_free=77%                                                                                                                                                                                                                         
 
Databases with other segment arrangements:
==========================================
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
 db_name=dbccdb:  total_size_mb=120MB total_other_mb=100MB total_other_free=71264KB total_other_percent=69% total_log_free=20368KB total_log_percent_free=99%                                                                                                                                                                                                                                                                                                                                                                     
 db_name=segtest:  total_size_mb=80MB total_other_mb=20MB total_other_free=56808KB total_other_percent=277% total_log_free=20368KB total_log_percent_free=99%                                                                                                                                                                                                                                                                                                                                                                     
(return status = 0)
                                                                                                                                                                                                                                                                                                                                                                               


Stored Procedure T-SQL

use sybsystemprocs
go

create proc sp_dbsummary
as
-- Summary listing of devices for SAP sybase ASE 15.7
-- Alastair Cook 12th Sept 2013
-- non-SAP stored procedure

set nocount on
declare @pagekb unsigned int

select @pagekb = (low / 1024)           /* kbytes per page */
from master.dbo.spt_values
where number = 1
and type = 'E'

select dbid, db_name=db_name(dbid), segmap, lstart, size, unreservedpgs, curunreservedpgs=1, 
size_mb=1,curunreserved_kb=1
into #sysusages_tmp from master.dbo.sysusages

update #sysusages_tmp 
set curunreservedpgs = curunreservedpgs(dbid, lstart, unreservedpgs)

update #sysusages_tmp
set size_mb=(@pagekb*size)/1024

update #sysusages_tmp
set  curunreserved_kb=@pagekb*curunreservedpgs

-- Added in tot_data and tot_log as pushpullsigns wrong !!
select dbid, db_name, size_mb,tot_seg_size=sum(size_mb), curunreserved_kb=sum(curunreserved_kb), segmap,segmap_desc="other" 
into #summary_1 
from #sysusages_tmp 
group by db_name, segmap

update #summary_1 set segmap_desc="data" where segmap = 3
update #summary_1 set segmap_desc="log" where segmap = 4
update #summary_1 set segmap_desc="mixed" where segmap = 7


select db_name, curunreserved_kb
into #summary_3
from #summary_1
where segmap_desc="other" and segmap != 4

select db_name, tot_curunreserved_kb=sum(curunreserved_kb)
into #summary_4
from #summary_3
group by db_name

select db_name,total_size_mb=sum(size_mb),
total_data_mb=0, total_log_mb=0, total_mixed_mb=0, total_other_mb=0,
total_data_free=0,total_log_free=0,total_mixed_free=0, total_other_free=0,
total_log_percent_free=0,total_data_percent_free=0, total_mixed_percent_free=0, total_otherpercent_free=0
into #summary_2
from #summary_1 
group by db_name

update #summary_2 
set two.total_data_mb = one.tot_seg_size,
two.total_data_free=one.curunreserved_kb
from #summary_2 two, #summary_1 one
where one.db_name=two.db_name
and one.segmap = 3

update #summary_2
set two.total_log_mb = one.tot_seg_size,
two.total_log_free=one.curunreserved_kb
from #summary_2 two, #summary_1 one
where one.db_name=two.db_name
and one.segmap = 4


update #summary_2
set two.total_mixed_mb = one.tot_seg_size,
two.total_mixed_free=one.curunreserved_kb
from #summary_2 two, #summary_1 one
where one.db_name=two.db_name
and one.segmap = 7

update #summary_2
set two.total_other_mb = one.tot_seg_size,
two.total_other_free=one.curunreserved_kb
from #summary_2 two, #summary_1 one
where one.db_name=two.db_name
and one.segmap not in (3,4,7)

-- First for mixed log and data databases 
print " "
print "Mixed data and log databases:"
select 
"db_name=" + db_name + ": " +
" total_size_mb=" + convert(varchar,total_size_mb) + "MB" +
" total_mixed_free_kb=" + convert(varchar,total_mixed_free) + "KB" +
" total_mixed_percent_free=" + convert(varchar,(100*total_mixed_free/(1024*total_size_mb))) + "%"
from #summary_2
where total_mixed_mb > 0
order by db_id(db_name)

update #summary_2
set total_log_free=@pagekb*lct_admin("logsegment_freepages", db_id(db_name))
where  total_data_mb > 0 and total_log_mb > 0



-- Then for data and log databases
print " "
print "Separate data and log databases:"
print "================================"
select
"db_name=" + db_name + ": " +
" total_size_mb=" + convert(varchar,total_size_mb) + "MB" +
" total_data_mb=" + convert(varchar,total_data_mb) + "MB" +
" total_log_mb=" +  convert(varchar,total_log_mb) + "MB" +
" total_data_free=" + convert(varchar,total_data_free) + "KB" +
" total_log_free=" + convert(varchar,total_log_free) + "KB" +
" total_data_percent_free=" + convert(varchar,(100*total_data_free/(1024*total_data_mb))) + "%" +
" total_log_percent_free=" + convert(varchar,(100*total_log_free/ (1024*total_log_mb))) + "%"
from #summary_2
where total_data_mb > 0 and total_log_mb > 0
order by db_name

-- Summary showing above in MB Units for data free
print "Summary of Data Segment Free in MB"
print "----------------------------------"
select
"db_name=" + db_name + ": " +
" total_data_free=" + convert(varchar,total_data_free/1024) + "MB" +
" total_data_percent_free=" + convert(varchar,(100*total_data_free/(1024*total_data_mb))) + "%" 
from #summary_2
where total_data_mb > 0 and total_log_mb > 0
order by db_name


update #summary_2
set total_log_free=@pagekb*lct_admin("logsegment_freepages", db_id(db_name))
where total_other_mb > 0

update #summary_2
set s2.total_other_free=s4.tot_curunreserved_kb
from #summary_2 s2 , #summary_4 s4
where s2.db_name = s4.db_name

-- Then for other databases
print " "
print "Databases with other segment arrangements:"
print "=========================================="
select
"db_name=" + db_name + ": " +
" total_size_mb=" + convert(varchar,total_size_mb) + "MB" +
" total_other_mb=" + convert(varchar,total_other_mb) + "MB" + 
" total_other_free=" +  convert(varchar,total_other_free) + "KB" +
" total_other_percent=" + convert(varchar,(100*total_other_free/(1024*total_other_mb))) + "%" +
" total_log_free=" + convert(varchar,total_log_free) + "KB" +
" total_log_percent_free=" + convert(varchar,(100*total_log_free/ (1024*total_log_mb))) + "%"
from #summary_2
where total_other_mb > 0
order by db_name

go

Test First on your Development System

Sites are welcome to use the above code to provide a summary view of sybase databases and modify as required.

Contact Information

If there are any changes suggested for this page please contact me.

Alastair Cook email:
cookalastair @ btinternet.com

Last update to this page - 14th Sept 2013