Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
SQL Server Transparent Data Encryption Alternative - Free Webinar
 

Why SYSPOLICY_PURGE_HISTORY job fails in SQL Server 2008 Failover Cluster Instance


By:   |   Last Updated: 2010-06-23   |   Comments   |   Related Tips: More > Policy Based Management

Problem

I have completed the installation of SQL Server 2008 on a Windows Server 2008 cluster. However, I have noticed that SYSPOLICY_PURGE_HISTORY SQL Server Agent Job has started failing. I am new to SQL Server 2008 Failover Clustering and would like to know the steps which I need to follow in order to resolve SYSPOLICY_PURGE_HISTORY Job failure.

Solution

In SQL Server 2008, Microsoft has introduced a new feature called Policy Based Management. As a DBA now you can define policies in SQL Server 2008, for example; all tables within a user database should have at least one clustered index, validate database backup files exist, monitoring linked server configurations, etc. Once you have defined the policies, then it can be evaluated against the targets to check for compliance issues.

Each time when policies are checked for compliance the records are inserted in the below system tables within the MSDB database.

  • syspolicy_policy_execution_history_internal
  • syspolicy_policy_execution_history_details_internal
  • syspolicy_policy_category_subscriptions_internal

The SYSPOLICY_PURGE_HISTORY is a built-in SQL Server Agent Job which is configured to run once a day at 2 AM. When SYSPOLICY_PURGE_HISTORY SQL Server Agent Job is executed it will remove records from the above tables, except for the retention period records.

The reason behind the failure of the SYSPOLICY_PURGE_HISTORY Job in a SQL Server 2008 Failover Clustering environment is the Erase Phantom System Health Records Step (Step 3).  The value defined in the PowerShell script is wrong. You need to replace the SQL Server Computer Node name within the job step with that of the actual SQL Server Virtual name for the cluster instance.

Wrong Value in Erase Phantom System Health Records Job Step

(Get-Item SQLSERVER:\SQLPolicy\SQLServerComputerNodeName\DEFAULT).EraseSystemHealthPhantomRecords()

Correct Value in Erase Phantom System Health Records Job Step

(Get-Item SQLSERVER:\SQLPolicy\SQLVirtualName\DEFAULT).EraseSystemHealthPhantomRecords()
In SQL Server 2008, Microsoft has introduced a new feature called Policy Based Management

Once you have made the above mentioned changes, click OK to save the changes within the SYSPOLICY_PURGE_HISTORY Job and then execute the Job and it should complete without errors.

Next Steps


Last Updated: 2010-06-23


next webcast button


next tip button



About the author
MSSQLTips author Ashish Kumar Mehta Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools