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 2 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, July 27, 2010 2:59 PM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 09, 2013 11:58 AM
Points: 415, Visits: 737

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

Post #959721

Elliott Whitlow

Posted Wednesday, July 28, 2010 12:05 PM

SSCertifiable

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

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

Post #960263

martintallett

Posted Thursday, September 02, 2010 2:59 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, November 23, 2011 12:15 AM
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?

Post #979361

vereshx

Posted Thursday, September 01, 2011 1:09 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 09, 2013 5:36 AM
Points: 29, Visits: 156

Hello,

You should try Data File facet with @VolumeFreeSpace condition.

Vereshx
SQL Server DBA

Post #1168494

Ken Hemmerling

Posted Tuesday, November 22, 2011 10:25 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 13, 2013 3:42 PM
Points: 3, Visits: 29

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

Post #1210355

vereshx

Posted Tuesday, November 22, 2011 2:26 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 09, 2013 5:36 AM
Points: 29, Visits: 156

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.

Post #1210546

Ken Hemmerling

Posted Tuesday, November 22, 2011 2:48 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 13, 2013 3:42 PM
Points: 3, Visits: 29

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

Post #1210566

vereshx

Posted Wednesday, November 23, 2011 12:08 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 09, 2013 5:36 AM
Points: 29, Visits: 156

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

Post #1210753

phynes

Posted Friday, March 16, 2012 8:34 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 16, 2012 9:13 AM
Points: 1, Visits: 1

Hi,

Im struggling to set an email alert for this?

Can you help?

Thanks

Post #1268280

vereshx

Posted Saturday, March 17, 2012 1:41 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 09, 2013 5:36 AM
Points: 29, Visits: 156

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

Post #1268686

« Prev Topic | Next Topic »

21 posts, Page 2 of 3

««1 2 3 »»

Permissions