Monday 14 November 2011

monitor sybase tempdb log space

You can use the below script which can be also be configured to send an email alert if the sybase tempdb free data or log space falls below 10% . i.e., tempdb
monitoring script.

select "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 and dbid=2

you have to use conditional shell commands to check if the Log/Data free < threshold.

No comments:

Post a Comment