Percentage of database free space

[xdai]

Hi, I would like to set up this metric for a sql 2000 db server, but the query doesn't work, can you please assist. thanks

[chriskelly]

I'm sorry, but we need more information.

Please can you email support@red-gate.com, quote the ticket number F0069786 in the subject line. Please can you include details of what you are trying to do.
_________________
Chris Kelly
Technical Support Engineer

[xdai]

on the custome metric for calculate percentage of database free space as below, doesn't work for sql 2000 database we try to monitor, do you have one could be use? thanks


SELECT (AU.unallocated + AU.unused) * 100 / (AU.reserved + AU.unallocated) AS free_space_pct
FROM (SELECT AU.reserved,
AU.reserved - AU.used AS unused,
CASE WHEN SF.db_size >= AU.reserved THEN (SF.db_size - AU.reserved) ELSE 0 END AS unallocated
FROM (SELECT SUM(CAST(CASE WHEN SF.status & 64 = 0 THEN SF.size ELSE 0 END AS DECIMAL(38, 2))) AS db_size
FROM dbo.sysfiles AS SF) AS SF
CROSS JOIN
(SELECT SUM(CAST(A.total_pages AS DECIMAL(38, 2))) AS reserved,
SUM(CAST(A.used_pages AS DECIMAL(38, 2))) AS used
FROM sys.partitions AS P
INNER JOIN
sys.allocation_units AS A
on A.container_id = P.partition_id) AS AU) AS AU;

[chriskelly]

the help that we have available can be found on our custom metric site

http://sqlmonitormetrics.red-gate.com/

as a general rule, if you can run the t-sql query in SSMS (or appropriate console) then it should work within a custom metric (as long as it is within the parameters specified e.g. returns a single integer value).
_________________
Chris Kelly
Technical Support Engineer