SAP Sybase ASE 15.7 sp_devicesummary Device Summary



sp_devicesummary Device Summary

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

An optional parameter will show only devices with space in mb above the specified value.

Sample Output

1> sp_devicesummary 1200
2> go
                                                                                                                                                                                                                                                                                                                                                                                 
 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  device=DATA002, db_name=altest_new, total_device_size_mb=9000, alloc_fragment_mb=4, device_free_space_mb=8386                                                                                                                                                                                                                                                                  
  device=DATA002, db_name=test_tempdb, total_device_size_mb=9000, alloc_fragment_mb=110, device_free_space_mb=8386                                                                                                                                                                                                                                                               
  device=DATA002, db_name=pushpullsigns, total_device_size_mb=9000, alloc_fragment_mb=500, device_free_space_mb=8386                                                                                                                                                                                                                                                             
  device=LOG004, db_name=primary_test, total_device_size_mb=5000, alloc_fragment_mb=500, device_free_space_mb=4500                                                                                                                                                                                                                                                               
  device=TESTSIZE_5G, db_name=MOVE_TEST, total_device_size_mb=5120, alloc_fragment_mb=5, device_free_space_mb=5115                                                                                                                                                                                                                                                               

(5 rows affected)

sp_devicesummary may be used with a parameter value in mb

eg sp_devicesummary 200
will show devices with more than 200 mb free space

(return status = 0)


Stored Procedure T-SQL

use sybsystemprocs
go

create proc sp_devicesummary @min_mb_free int = -1
as
-- Summary listing of devices SAP sybase 15.7
-- Alastair Cook 12th Sept 2013

declare @pagekb unsigned int

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


select vdevno=d.vdevno,device_name=d.name, db_name=db_name(u.dbid),
dev_size=(d.high-d.low+1)/512, alloc_mb=(@pagekb*u.size)/1024, segmap=u.segmap
into #devices
from master..sysusages u, master..sysdevices d
where u.vdevno =* d.vdevno
and d.status != 16

update #devices set alloc_mb = 0, db_name="!! NOT_ALLOCATED !!" where alloc_mb is null

select device_summary=(" device=" + device_name + ", db_name=" + db_name + "," +
" total_device_size_mb=" + convert(varchar,dev_size) + "," + " alloc_fragment_mb=" + convert(varchar,alloc_mb)),
free_space_mb=(dev_size - sum(alloc_mb))
into #device_summary
from #devices
group by vdevno

select device_summary + "," + " device_free_space_mb=" + convert(varchar, free_space_mb) from #device_summary
where free_space_mb > @min_mb_free

print " "
print "sp_devicesummary may be used with a parameter value in mb"
print " "
print "eg sp_devicesummary 200"
print "will show devices with more than 200 mb free space"
print " "

drop table #devices
drop table #device_summary
go

Test First on your Development System

Sites are welcome to use the above code to provide a summary view of sybase devices 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 - 6th Sept 2013