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 123»»»

Using SQL Server 2008 PBM to Monitor Free Space Expand / Collapse
Author
Message
Posted Tuesday, December 15, 2009 11:40 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 17, 2014 4:04 PM
Points: 418, Visits: 785
Comments posted to this topic are about the item Using SQL Server 2008 PBM to Monitor Free Space

Thanks
Jay
http://www.sqldbops.com
Post #834866
Posted Wednesday, December 16, 2009 7:10 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, November 21, 2014 4:43 PM
Points: 1,357, Visits: 1,726
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) ?
Post #835055
Posted Wednesday, December 16, 2009 9:32 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 17, 2014 4:04 PM
Points: 418, Visits: 785
Thanks for your comments...
No specific reason, both methods will give the same result...


Thanks
Jay
http://www.sqldbops.com
Post #835235
Posted Wednesday, December 16, 2009 3:21 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
I'm curious what value you receive from "data file free space" as opposed to something like disk volume free space.

CEWII
Post #835471
Posted Wednesday, December 16, 2009 5:18 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 17, 2014 4:04 PM
Points: 418, Visits: 785
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


Thanks
Jay
http://www.sqldbops.com
Post #835500
Posted Thursday, December 17, 2009 9:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
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
Post #835782
Posted Thursday, December 17, 2009 7:25 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 17, 2014 4:04 PM
Points: 418, Visits: 785
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
Jay
http://www.sqldbops.com
Post #836093
Posted Monday, March 1, 2010 5:28 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 9:07 AM
Points: 304, Visits: 948
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
Post #874804
Posted Tuesday, March 2, 2010 2:54 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 17, 2014 4:04 PM
Points: 418, Visits: 785
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.


Thanks
Jay
http://www.sqldbops.com
Post #875561
Posted Tuesday, July 27, 2010 2:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 27, 2010 7:16 PM
Points: 1, Visits: 1
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
Post #959699
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse