Using SQL Server 2008 PBM to Monitor Free Space

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jayakumar Krishnan » Using SQL Server 2008 PBM to Monitor Free...

21 posts, Page 1 of 3

1 2 3 »»»

Using SQL Server 2008 PBM to Monitor Free Space

Rate Topic

Display Mode

Topic Options

Author

Message

Jayakumar Krishnan

Posted Tuesday, December 15, 2009 11:40 PM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 5:52 PM
Points: 415, Visits: 738

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

sknox

Posted Wednesday, December 16, 2009 7:10 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 9:31 AM
Points: 1,041, Visits: 1,356

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

Jayakumar Krishnan

Posted Wednesday, December 16, 2009 9:32 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 5:52 PM
Points: 415, Visits: 738

Thanks for your comments...
No specific reason, both methods will give the same result...

Thanks
Jay
http://www.sqldbops.com

Post #835235

Elliott Whitlow

Posted Wednesday, December 16, 2009 3:21 PM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 12:02 PM
Points: 5,854, Visits: 4,873

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

CEWII

Post #835471

Jayakumar Krishnan

Posted Wednesday, December 16, 2009 5:18 PM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 5:52 PM
Points: 415, Visits: 738

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

Elliott Whitlow

Posted Thursday, December 17, 2009 9:59 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 12:02 PM
Points: 5,854, Visits: 4,873

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

Jayakumar Krishnan

Posted Thursday, December 17, 2009 7:25 PM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 5:52 PM
Points: 415, Visits: 738

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

PHXHoward

Posted Monday, March 01, 2010 5:28 PM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 4:32 PM
Points: 250, Visits: 693

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

Jayakumar Krishnan

Posted Tuesday, March 02, 2010 2:54 PM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 5:52 PM
Points: 415, Visits: 738

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

res04qu7

Posted Tuesday, July 27, 2010 2:32 PM

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

21 posts, Page 1 of 3

1 2 3 »»»

Permissions