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.
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;
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).
xdai
Posts: 98
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).