Percentage of database free space

SQL Server performance monitoring and alerting

Moderators: eddie davis, priyasinha, Adam, chriskelly, Chris Lambrou, Chris Spencer

Percentage of database free space

Postby xdai » Mon Feb 18, 2013 4:13 pm

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
xdai
 
Posts: 69
Joined: Wed Dec 31, 2008 11:50 pm

Postby chriskelly » Tue Feb 19, 2013 5:15 pm

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
chriskelly
 
Posts: 330
Joined: Mon Apr 19, 2010 1:44 pm
Location: Cambridge, UK

Postby xdai » Tue Feb 19, 2013 5:33 pm

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;
xdai
 
Posts: 69
Joined: Wed Dec 31, 2008 11:50 pm

Postby chriskelly » Tue Mar 05, 2013 1:16 pm

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
chriskelly
 
Posts: 330
Joined: Mon Apr 19, 2010 1:44 pm
Location: Cambridge, UK


Return to SQL Monitor 3

Who is online

Users browsing this forum: No registered users and 0 guests