March 1, 2010 at 2:35 pm
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
March 1, 2010 at 2:45 pm
Does this help?
March 1, 2010 at 4:39 pm
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
March 1, 2010 at 4:59 pm
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.
September 1, 2011 at 1:05 am
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
April 2, 2013 at 7:23 am
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.
April 2, 2013 at 12:55 pm
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. 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply