SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Available disk space policy


Available disk space policy

Author
Message
PHXHoward
PHXHoward
Right there with Babe
Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)

Group: General Forum Members
Points: 763 Visits: 1237
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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: Administrators
Points: 65281 Visits: 19118
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
My Blog: www.voiceofthedba.com
PHXHoward
PHXHoward
Right there with Babe
Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)

Group: General Forum Members
Points: 763 Visits: 1237
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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: Administrators
Points: 65281 Visits: 19118
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
My Blog: www.voiceofthedba.com
vereshx
vereshx
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
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
lmacdonald
lmacdonald
Old Hand
Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)

Group: General Forum Members
Points: 328 Visits: 538
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.
Loundy
Loundy
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1844 Visits: 1190
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 Smile You might want to have a play around to see what gives the best results.

Hope this helps. Smile

There's no kill switch on awesome!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search