Why SYSPOLICY_PURGE_HISTORY job fails in SQL Server 2008 Failover Cluster Instance
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.
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.
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
Correct Value in Erase Phantom System Health Records Job Step
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.
- 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
Last Updated: 2010-06-23
About the author
View all my tips