Red Gate forums :: View topic - Percentage of database free space
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Monitor 3
SQL Monitor 3 forum

Percentage of database free space

Search in SQL Monitor 3 forum
Post new topic   Reply to topic
Jump to:  
Author Message
xdai



Joined: 31 Dec 2008
Posts: 69

PostPosted: Mon Feb 18, 2013 4:13 pm    Post subject: Percentage of database free space Reply with quote

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
Back to top
View user's profile Send private message
chriskelly



Joined: 19 Apr 2010
Posts: 328
Location: Cambridge, UK

PostPosted: Tue Feb 19, 2013 5:15 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
xdai



Joined: 31 Dec 2008
Posts: 69

PostPosted: Tue Feb 19, 2013 5:33 pm    Post subject: Reply with quote

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;
Back to top
View user's profile Send private message
chriskelly



Joined: 19 Apr 2010
Posts: 328
Location: Cambridge, UK

PostPosted: Tue Mar 05, 2013 1:16 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group