Using SQL Server 2008 PBM to Monitor Free Space

  • Jayakumar Krishnan

    SSCarpal Tunnel

    Points: 4710

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

  • sknox

    SSChampion

    Points: 12227

    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) ?

  • Jayakumar Krishnan

    SSCarpal Tunnel

    Points: 4710

    Thanks for your comments...

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

  • Elliott Whitlow

    SSC Guru

    Points: 102296

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

    CEWII

  • Jayakumar Krishnan

    SSCarpal Tunnel

    Points: 4710

    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.

    Thanks

    Jay

  • Elliott Whitlow

    SSC Guru

    Points: 102296

    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.

    CEWII

  • Jayakumar Krishnan

    SSCarpal Tunnel

    Points: 4710

    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.

  • PHXHoward

    SSCrazy Eights

    Points: 8221

    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.

    Howard

  • Jayakumar Krishnan

    SSCarpal Tunnel

    Points: 4710

    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.

  • res04qu7

    SSC Journeyman

    Points: 79

    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?

    Thanks

  • Jayakumar Krishnan

    SSCarpal Tunnel

    Points: 4710

    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.

  • Elliott Whitlow

    SSC Guru

    Points: 102296

    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.

    CEWII

  • martintallett

    Newbie

    Points: 5

    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?

  • vereshx

    SSC Journeyman

    Points: 78

    Hello,

    You should try Data File facet with @VolumeFreeSpace condition.

    Vereshx

    SQL Server DBA

  • Ken Hemmerling

    SSC Enthusiast

    Points: 164

    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.

    Ken

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

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