syspolicy_purge_history What is it?

Yesterday I was going through the job settings of one of the Production Instances. I saw a job named syspolicy_purge_history running every day at 2 AM.

For a moment I could not realize what was this job for and who had created it. Then I recollected that this instance was recently migrated from SQL Server 2005 to SQL Server 2008. This job is created by default when an instance of SQL Server 2008 is installed or upgraded to SQL Server 2008.

SQL Server 2008 introduced a new feature called Policy Based Management. A Policy can be something like xp_cmdshell should not be enabled on an instance. When a policy runs it stores the results in MSDB. Over a period of time, this may will cause unnecessary data getting piled up in MSDB. The job syspolicy_purge_history prunes the data older than the the days defined in HistoryRetentionInDays property of Policy Management.

In the above screenshot it is set to 0 which means that the Policy Evaluation History will never be deleted. So on this instance the job is running just like that 😉

If this job is missing for some reason, it can always be recreated using the below query

EXEC msdb.dbo.sp_syspolicy_create_purge_job

One thought on “syspolicy_purge_history What is it?

  1. Pingback: Clean Up History – nice and tidy. | What's Weird in SQL this week

Comments are closed.