After a brand new Sql Server 2008 installation or upgrade from a previous version, a job will be created during the installation/upgrade process: syspolicy_purge_history. What does it do?
Before answering that question, here is a little background. Sql Server 2008 introduced a new feature called Policy Management. For example, one could define a policy that says all tables must have a clustered index. Once defined, the policy can be evaluated against targets to check for compliance. Each evaluation of compliance will be recorded in tables in msdb, syspolicy_policy_execution_history_details_internal, syspolicy_policy_execution_history_internal, and syspolicy_policy_category_subscriptions_internal, specifically. The evaluation condition is built on top of object facets, which is predefined by Microsoft.
The purpose of that job, syspolicy_purge_history, is to remove records in those tables to maintain so it does not pile up and take up space unnecessarily. It has three steps. The first step verifies if Policy Management is enabled. If yes, it will go to the second step, which deletes policy evaluation history prior to cutoff date, defined by HistoryRetentionInDays. If Policy Management is not enabled, the whole job stops with an error. I haven’t quite figured out what the third step does, which is a PowerShell step. It calls the PolicyStore.EraseSystemHealthPhantomRecords method, and no detailed document for it was available as of this writing.
By the way, here is the code to create AllTablesNeedClusteredIndex condition. Another thing that is worth knowing is that if you only want to check a condition against only user databases, not system databases, the database facet to check against is called IsSystemObject.
Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’AllTablesNeedClusteredIndex’, @description=N”, @facet=N’Table’, @expression=N’