Why SYSPOLICY_PURGE_HISTORY job fails in SQL Server 2008 Failover Cluster Instance


By:   |   Updated: 2010-06-23   |   Comments   |   Related: 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


get scripts

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





Comments For This Article





download


Recommended Reading

Using Policy Based Management in SQL Server

Internal SQL Server Certificates have expired is this an issue

Enforce SQL Server Database Naming Conventions Using Policy Based Management

Configuring Alerts for SQL Server Policy Based Management

Monitor Your SQL Server Virtual Log Files with Policy Based Management





get free sql tips
agree to terms


Learn more about SQL Server tools