When you install or upgrade SQL Server 2008 or R2 you will see a new SQL Server Agent job named syspolicy_purge_history. In fact this is the only job you will see after installing a brand new SQL Server 2008 instance.
This job is relevant to Policy Based Management and by default it has a daily schedule. With Policy Based Management you have the option of defining policies for your server. For example, you can define a policy that all table names should start with the tbl prefix or else you can define every table must contain a clustered index etc.
Once the policies are defined, the policy can be evaluated against targets to check for compliance. This will result populating of following tables in msdb.
syspolicy_policy_execution_history_details_internal
syspolicy_policy_execution_history_internal
syspolicy_policy_category_subscriptions_internal
Since these tables will be filled with lot of data, task of the job is to remove the previous records. However with the cluster environment in place, this third or the last step might fail with the following error message.
A job step received an error at line 1 in a PowerShell script. The corresponding line is ‘(Get-Item SQLSERVER:SQLPolicyDBIS1).EraseSystemHealthPhantomRecords()’. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘Cannot retrieve the dynamic parameters for the cmdlet. SQL Server PowerShell provider error: Could not connect to ‘ DBIS1’. [Failed to connect to server DBIS1. –> A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)] ‘. Process Exit Code -1. The step failed.
If you examine further the job step is:
(Get-Item SQLSERVER:SQLPolicyDBIS1).EraseSystemHealthPhantomRecords()
Notice that DBIS1 is specified as the SQL Server Instance although it is not the actual SQL Server instance which you have to change this manually.
In case of SQL Server 2008 R2 things have changed and the is the step.
if (‘$(ESCAPE_SQUOTE(INST))’ -eq ‘MSSQLSERVER’) {$a = ‘DEFAULT’} ELSE {$a = ”};
(Get-Item SQLSERVER:SQLPolicy$(ESCAPE_NONE(SRVR))$a).EraseSystemHealthPhantomRecords()
Thus you do not have to worry about the node name and job will read from the powershell script.
]]>