Percentage of database free space
xdai
Posts: 98
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
Comments
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.
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;
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).