SQL script to calculate sybase database size ,data free,data used,Total log space,log space free,log space used.
This is one of the useful and frequently used sql scripts by any sybase DBA.

I am getting back into support Sybase for some new projects. They are running 15.0.2 and when I run this code I see 2 issues.

1. A divide by zero error msg. It causes the sql to stop after the 1st 5 or so databases. If I added an order by 1 clause it processes all databases, but still spits out the error.

2. The device sizes are off by a factor or 4.

Any suggestions as how I should approach adjusting the code would be appreciated.
Thanks
Joe

(03-29-2009 04:08 PM)sybaseteam Wrote:  SQL script to calculate sybase database size ,data free,data used,Total log space,log space free,log space used.
This is one of the useful and frequently used sql scripts by any sybase DBA.

Code:

select  "Database," = convert(char(20),db_name(dbid))+',',
"Data Size," = str(sum(size * abs(sign(segmap - 4))) / 512.0, 7, 2)+',',
"Data Used," = str(sum((size - curunreservedpgs(dbid, lstart, unreservedpgs)) * abs(sign(segmap - 4))) / 512.0, 7, 2)+',',
"Data Free," = str(100.0 * sum((curunreservedpgs(dbid, lstart,unreservedpgs)) * abs(sign(segmap - 4))) / sum(size * abs(sign(segmap- 4))), 3) + "%"+',',
"Log Size," = str(sum(size * (1 - abs(sign(segmap - 4)))) / 512.0, 7, 2)+',',
"Log Used," = str(sum((size - curunreservedpgs(dbid, lstart, unreservedpgs))* (1 - abs(sign(segmap - 4)))) / 512.0, 7, 2)+',',
"Log Free" = str(100.0 * sum((curunreservedpgs(dbid, lstart,unreservedpgs))* (1 - abs(sign(segmap - 4)))) / sum(size * (1 - abs(sign(segmap - 4)))), 3) + "%"
from master..sysusages
where segmap < 5
group by db_name(dbid)

Hi Joe,

As compare to ASE 12.5 to 15.x Sybase has only removed the vdev no from the sysusages table as this query is using only sysusages table. This script is only for the 2k page size server.

I am modifying it for all page size(2k,4k..16k) and will let you know the final script, in the mean while can you please paste the excat error message?

Thanks.

if i want to include some other devices like seg_index ??