Why SYSPOLICY_PURGE_HISTORY job fails in SQL Server 2008 Failover Cluster Instance

By: Ashish Kumar Mehta   |   Updated: 2010-06-23   |   Comments   |   Related: > 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()

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

• Review Using Policy-Based Management in SQL Server 2008
• Review Validate SQL Server Backups Exist with Policy Based Management
• Read more tips on Policy Based Management
• Read my previous tips

About the author

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips