Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using SQL Server 2008 PBM to Monitor Free Space


Using SQL Server 2008 PBM to Monitor Free Space

Author
Message
Jayakumar Krishnan
Jayakumar Krishnan
SSC-Addicted
SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)

Group: General Forum Members
Points: 418 Visits: 809
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
Elliott Whitlow
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6208 Visits: 5314
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
martintallett
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 7
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
vereshx
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 172
Hello,

You should try Data File facet with @VolumeFreeSpace condition.




Vereshx
SQL Server DBA
Ken Hemmerling
Ken Hemmerling
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 78
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
vereshx
vereshx
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 172
Well, it seems that the user you are using to evaluate the policy does not have enough rights to run DBCC SHOWFILESTATS. You should try adding the user to the sysadmin role. If however the user is already a sysadmin… than I would be quite astonished.
Ken Hemmerling
Ken Hemmerling
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 78
You are correct. When I evaluate the Policy directly, it works. My problem is that when I tried to schedule an Agent job, it runs as a service account defined on my domain which clearly doesn't have the appropriate permissions.

Another question though: I see the policy in syspolicy_policies (in the msdb database) and I see the correct start and end time in syspolicy_policy_execution_history. What I DON'T see is anything in syspolicy_policy_execution_history_details which is odd because when I evaluate the policy and click on the link for the details, I see the expected and actual values. Do you think that Microsoft changed how they stored the results? Any idea if it's possible to retrieve the results via a SQL query?

Thanks

Ken
vereshx
vereshx
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 172
Hi Ken,
In the syspolicy_policy_execution_history view you can see a record for each time the policy has been evaluated. One might think that this would imply that the second view, syspolicy_policy_execution_history_details, should have entries related to the policies evaluated; unfortunately this is not the case. By default, SQL Server 2008 (and later) will only collect and display history in this view then the policy fails.
If you want to change this, you should right-click the Policy Management node in SSMS, under Management Category, and choose Properties. You will find only a General Tab with 3 options. The option to be set is LogOnSuccess and you should choose True for its value (the default is False).
That should solve the syspolicy_policy_execution_history_details logging problem.


Vereshx
SQL Server DBA
phynes
phynes
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1
Hi,

Im struggling to set an email alert for this?

Can you help?

Thanks
vereshx
vereshx
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 172
Hello,

Please refer to this article: http://msdn.microsoft.com/en-us/library/bb510667.aspx
Jump to the Configuring Alerts to Notify Policy Administrators of Policy Failures section of the mentioned article. This shows the message number that can be used to define your alert.

Vereshx
SQL Server DBA
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search