Using SQL Server 2008 PBM to Monitor Free Space

Question

Using SQL Server 2008 PBM to Monitor Free Space

Viewing 6 posts - 16 through 20 (of 20 total)

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

vereshx SSC Journeyman Points: 78 More actions · Answer 1

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 SSC Enthusiast Points: 164 More actions · Answer 2

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 SSC Journeyman Points: 78 More actions · Answer 3

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

Hi,

Im struggling to set an email alert for this?

Can you help?

Thanks

vereshx SSC Journeyman Points: 78 More actions · Answer 5

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

BoobyB SSCertifiable Points: 5470 More actions · Answer 6

This has been a very informative article... I am trying to set up alerts to notify me when the database file growth goes above 80 . ( same for the log file)... the issue i am having is simple and I assume lots of people come to about this same situation.

in my server some databases are set up as restricted growth and some as unlimited growth ( grow till the size of the volume is reached).

now if lets say i have a database growth set unlimited and the initial data file size is 20 gb.... when the file reached 16gb it raises the alert but the volume still has 100 gb free space left.. so the alert becomes a false flag.

i want only to be notified when the database reached 80% threshold and volume has no space left. Can this be done?

if not this way ...any other way comes in to the mind please let me know.

Regards,