Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Available disk space policy Expand / Collapse
Author
Message
Posted Monday, March 1, 2010 2:35 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:04 PM
Points: 298, Visits: 913
Hi everyone. I designated a SQL Server 2008 instance to act as a central management server. It is running a really nice tool called Enterprise Policy Management Framework that reports policy failures on a dashboard.

I would like to design a policy that looks at the disk space that I am using to store my SQL Server backups and reports when the space is low.

For example, a policy that evaluates <used drive space of backup volume> <= <80% of total drive space of backup volume> so that I have a warning when my backups might soon fail.

I found a field called @AvailableSpace in the policy wizard but I don't see one for total space.

Am I approaching this right?

Thanks much,

Howard
Post #874725
Posted Monday, March 1, 2010 2:45 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:10 PM
Points: 33,095, Visits: 15,202
Does this help?
http://www.sqlservercentral.com/articles/PBM/68602/







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #874736
Posted Monday, March 1, 2010 4:39 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:04 PM
Points: 298, Visits: 913
Thank you Steve! This policy is now in place and working properly. I am really impressed with this documentation that matched so closely with what I was attempting to do.

I am confused by the results however. In the results there is a database that is 350mb and sitting on a 300gb drive with 134gb free. Why does this database show up as 10% or less available space? I followed the documentation and choose shrink file and sure enough, it says it only has 5% available. The database is listed as Autogrowth by 10 percent, unrestricited growth. Is there some sort of database growth restriction imposed that I'm not seeing.

Going forward, my need is to find backup drives that are not large enough to hold a full database backup so that I can clean them up prior to the next full backup. Can this policy be made to total the size of all the databases and check if there is enough free disk space to hold a "backup all databases" maintenance plan?

Thanks again.

Howard
Post #874790
Posted Monday, March 1, 2010 4:59 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:10 PM
Points: 33,095, Visits: 15,202
It wasn't me, Howard, but glad it helped.

If you go to that article and click the "discuss" link, you can ask the author a question and I'm sure he will help.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #874794
Posted Thursday, September 1, 2011 1:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 5:18 AM
Points: 42, Visits: 172
Hi guys,

If you want to find disk drives that are not large enough for containing a database / database file you should try to configure a policy using the Data File Facet and specify the condition @VolumeFreeSpace. I don't know if this will solve your backup problems, but nevertheless, it's a good start.





Vereshx,
SQL Server DBA
Post #1168492
Posted Tuesday, April 2, 2013 7:23 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 8:28 AM
Points: 70, Visits: 321
Hey Guys,

I have been trying to figure this out to. The link in this thread sets a policy that determines free space in the .mdf file. I don't care about the file size itself, I just want an alert so that if a backup location runs below %10 of free space it will fail the policy. I assume I use the @volumefreespace option but I can't figure out what I should put for a value.

There must be something I can divide or multiply the @volumefreespace to get 10% of the total space of the drive the file is on. Yes I suck at math.
Post #1437880
Posted Tuesday, April 2, 2013 12:55 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 8:05 AM
Points: 1,597, Visits: 1,150
You could try using a WQL query - something like the following:



Multiply(Divide(ExecuteWql('Numeric', 'root\CIMV2', 'SELECT FreeSpace FROM Win32_LogicalDisk WHERE DeviceID ="D:"'), ExecuteWql('Numeric', 'root\CIMV2', 'SELECT Size FROM Win32_LogicalDisk WHERE DeviceID ="D:"')), 100)





This would be put in the Expression>Field cell (click the ... and directly paste the code). Then simply choose the >= operator and then give your percentage value that you want to alert on. Remember to change the drive letter to the drive you want to monitor.

I'm not sure what facet to use mind, I only played around for a few minutes to try and give you a solution :) You might want to have a play around to see what gives the best results.

Hope this helps. :)


There's no kill switch on awesome!
Post #1438061
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse