Using SQL Server 2008 PBM to Monitor Free Space

Question

Using SQL Server 2008 PBM to Monitor Free Space

Jayakumar Krishnan

SSCarpal Tunnel

Points: 4710
More actions
December 15, 2009 at 11:40 pm

#385952

Comments posted to this topic are about the item Using SQL Server 2008 PBM to Monitor Free Space
Thanks
Jay
http://www.sqldbops.com

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

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

sknox SSChampion Points: 12388 More actions · Answer 1

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 More actions · Answer 2

Thanks for your comments...

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

Thanks
Jay
http://www.sqldbops.com

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 3

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 More actions · Answer 4

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

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 5

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 More actions · Answer 6

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

PHXHoward SSCrazy Eights Points: 8388 More actions · Answer 7

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 More actions · Answer 8

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

res04qu7 SSC Journeyman Points: 79 More actions · Answer 9

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 More actions · Answer 10

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.

Thanks
Jay
http://www.sqldbops.com

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 11

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 More actions · Answer 12

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 More actions · Answer 13

Hello,

You should try Data File facet with @VolumeFreeSpace condition.

Vereshx

SQL Server DBA

Ken Hemmerling SSC Enthusiast Points: 164 More actions · Answer 14

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