SQL Server 2008: syspolicy_purge_history job

 

This job is created automatically when on SQL Server 2008 Policy Management is enabled.

Policy Management is a new feature introduced on SQL Server 2008 onwards.

Once the Policy Management defined, the policy can be evaluated against targets to check for compliance. Each evaluation of compliance will be recorded in tables in system database msdb,

1. syspolicy_policy_execution_history_details_internal
2. syspolicy_policy_execution_history_internal
3. syspolicy_policy_category_subscriptions_internal

The evaluation condition is built on top of object facets, which is predefined by Microsoft.

Purpose of the job, syspolicy_purge_history:

It is to remove records in those tables to maintain so it does not pile up and take up space unnecessarily. It has three steps

It does what instance it is running in and then erases system health records.

Step 1 : 
The first step verifies if Policy Management is enabled. If yes, it will go to the second step
IF (msdb.dbo.fn_syspolicy_is_automation_enabled() != 1)
        BEGIN
            RAISERROR(34022, 16, 1)
        END

step 2
Deletes policy evaluation history prior to cutoff date, defined by HistoryRetentionInDays.
EXEC msdb.dbo.sp_syspolicy_purge_history

step 3
It works out what instance it is running in and then erases system health records.

if ('$(ESCAPE_SQUOTE(INST))' -eq 'MSSQLSERVER') {$a = '\DEFAULT'} ELSE {$a = ''};
(Get-Item SQLSERVER:\SQLPolicy\$(ESCAPE_NONE(SRVR))$a).EraseSystemHealthPhantomRecords()