syspolicy_purge_history Job Fails

The syspolicy_purge_history SQLAgent job is used by Policy Based Management in SQL server to clear out the policy evaluation history kept by PBM.

The job consists of the following three steps.

1. First to check to see if PBM is enabled and exit if it is not.
2. Run the msdb.dbo.sp_syspolicy_purge_history system stored procedure to clear the history
3. Use PowerShell to clear the system health phantom records

If you’re not a PBM user you might not have any idea what this job does (until now).  This job might be failing on you and the error is seen in step 3.  If this describes you then you are also probably running your SQL instance on a cluster, and that’s why we see this job fail.

The job is created by default during the SQL server install and SQL uses the system name in step 3.  It’s not a problem on a standalone SQL server, but it’s a problem on a cluster.  When you address a SQL instance running on a cluster you don’t connect using the names of the physical cluster nodes.  You have to use the virtual cluster network name given to that SQL instance on the cluster.

The fix is very simple.  You just need to open the job and edit step 3 to replace the system name with the cluster virtual name.  You don’t even need to know PowerShell, so don’t panic if you don’t.  Simply replace the server name with the virtual cluster network name for you SQL instance.  Here’s an example of the PowerShell line.

(Get-Item SQLSERVER:SQLPolicyMyServerNameDEFAULT).EraseSystemHealthPhantomRecords()