Using SQL Server 2008 PBM to Monitor Free Space

  • Comments posted to this topic are about the item Using SQL Server 2008 PBM to Monitor Free Space

  • In this article I’m walking you through how to setup data file free space monitoring using SQL Server 2008 Policy-Based Management feature. You may think “Hey dude this is not a monitoring tool…!” but my answer would be “We can make this feature monitor data files free space on a database or instance”

    To which I would add, "and besides, what we're doing here is testing a databases adherence to a policy we have. What better place to do that, than a policy management tool?"

    Good article. I only have one question: is there a reason you did Multiply(@Size,Divide(10,100)) rather than just Divide(@Size,10) ?

  • Thanks for your comments...

    No specific reason, both methods will give the same result...

  • I'm curious what value you receive from "data file free space" as opposed to something like disk volume free space.


  • Using Data_File facet has multiple properties including @Size,@UsedSpace, if we Subtract(@Size,@UsedSpace) them then will get the current free space in kilobytes. From that value you can determine whether it meets your threshold policy or not.

    Hope I answered your question.



  • So you have a policy of how much free space is within the file, that may be fine for files that have a fair amount of changes but I like to see a very low number for ones that don't (or very little). I was questioning how useful this measurement is, the amount of free space in an already allocated file may be nice to know but I am generally a lot more concerned about free space on the volume that the file sits on, so that I can know that IF it needs more space it can get it.. That was what I was really asking about.


  • But unfortunately there is no facet in PBM for physical Disk volume as per my knowledge, if so it would have got Disk volume properties liek space values as well and that would be nice to get more hold on this measurement. But this article will list only if the data files free space reached/crossed your specified threshold.

  • Thanks, this is great documentation.

    Can this be modified to find backup drives that are not large enough to hold a full database backup? That way they can be cleaned up prior to the next full backup instead of after a failure. Can this policy be made to total the size of all the databases in the instance and check if there is enough free disk space to hold a "backup all databases" maintenance plan?

    Thanks again.


  • The PBM is focused only on SQL Server object but not the physical server objects. You can check/verify defined metric values of individual objects in an instance but can not sum up tasks.

    Hope I clarified your doubts.

  • Very informative article.

    I am trying to use policy to monitor space at the drive level (for example H: drive). How can I specify this in the condition?


  • Per my knowledge there is no specific facet for disk volume, but I could see system policies and conditions which covers volume space and I've not explored yet. You may try that one.

  • I also think you need to keep in mind that drives is not the most granular level of space allocation. You can have mounted volumes that have their own sizes and things like xp_fixeddrives won't report on them.


  • My training kit for SQL Server 2008 (70-432) suggests creating an alert to execute a job to expand the database. Is this necessary when the CREATE DATABASE statement has a FILEGROWTH option?

  • Hello,

    You should try Data File facet with @VolumeFreeSpace condition.


    SQL Server DBA

  • This looks like exactly what I need but when I try to run it, I get the following error:

    Exception encountered while executing policy Datafile_FreeSpace_Threshold_Policy.

    ---> Failed to retrieve data for this request. ---> User guest does not have permission to run DBCC showfilestats for database master.

    I'm not sure what I did to mess things up so much. Any help would be appreciated.


Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply