The syspolicy_purge_history SQL Server Agent job may fail in SQL Server 2008

SYMPTOMS
In Microsoft SQL Server 2008, the syspolicy_purge_history SQL Server Agent job may fail when you run the syspolicy_purge_history job on a clustered instance. You may receive an error message that resembles the following in the history log file for the syspolicy_purge_history job:
Date datetime
Log Job History (syspolicy_purge_history)

Step ID 3
Server SQLVirtualName\instancename
Job Name syspolicy_purge_history
Step Name Erase Phantom System Health Records.
Duration 00:00:33
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: user. The job script encountered the following errors. These errors did not stop the script: A job step received an error at line 1 in a PowerShell script. The corresponding line is '(Get-Item SQLSERVER:\SQLPolicy\SQLVirtualName\instancename).EraseSystemHealthPhantomRecords()'.

Correct the script and reschedule the job. The error information returned by PowerShell is: 'SQL Server PowerShell provider error: Could not connect to 'SQLVirtualName\instancename'. [Failed to connect to server SQLVirtualName\instancename. --> An error has occurred while establishing a connection to the server.

When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)] Failed to connect to server SQLVirtualName\instancename. An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) '

A job step received an error at line 1 in a PowerShell script. The corresponding line is '(Get-Item SQLSERVER:\SQLPolicy\SQLVirtualName\instancename).EraseSystemHealthPhantomRecords()'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot find path 'SQLSERVER:\SQLPolicy\SQLVirtualName\instancename' because it does not exist. ' A job step received an error at line 1 in a PowerShell script. The corresponding line is '(Get-Item SQLSERVER:\SQLPolicy\SQLVirtualName\instancename).EraseSystemHealthPhantomRecords()'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'You cannot call a method on a null-valued expression. '. Process Exit Code -1. The step failed.
CAUSE
This problem may occur if the syspolicy_purge_history job uses the computer node name instead of the virtual server name for the cluster instance.
RESOLUTION
To resolve this problem, use one of the following methods.

Method 1: Edit the syspolicy_purge_history job

Edit step 3 of the syspolicy_purge_history job. To do this, follow these steps:
  1. Start SQL Server Management Studio.
  2. Expand SQL Server Agent, and then expand Jobs.
  3. Right-click syspolicy_purge_history, and then click Properties.
  4. Click Steps.
  5. Click Erase Phantom System Health Records, and then click Edit.
  6. In the Command box, replace the computer node name by using the virtual server name for the cluster instance.
  7. Click OK, and then click Close.

Method 2: Re-create the syspolicy_purge_history job

To re-create the syspolicy_purge_history job, run the following Transact-SQL statement.
DECLARE @jobId uniqueidentifier-- Obtain the current job identifier that is associated with the PurgeHistorySELECT @jobId = CAST(current_value AS uniqueidentifier)FROM msdb.dbo.syspolicy_configuration_internalWHERE name = N'PurgeHistoryJobGuid'-- Delete the job identifier association in the syspolicy configurationDELETE FROM msdb.dbo.syspolicy_configuration_internalWHERE name = N'PurgeHistoryJobGuid'-- Delete the offending jobEXEC msdb.dbo.sp_delete_job @job_id = @jobId-- Re-create the job and its association in the syspolicy configuration tableEXEC msdb.dbo.sp_syspolicy_create_purge_job
MORE INFORMATION
In SQL Server 2008 Setup, the Setup script queries the database engine for the computer name. When the Setup script queries for the computer name, the database engine is not aware of the cluster. Therefore, the database engine returns the computer name instead of the cluster name.
"SQL Server 2008 Tools" SQL2008RelNoteTools
Properties

Article ID: 955726 - Last Review: 03/19/2009 22:28:40 - Revision: 2.0

Microsoft SQL Server 2008 Standard, Microsoft SQL Server 2008 Enterprise, Microsoft SQL Server 2008 Workgroup, Microsoft SQL Server 2008 Developer

  • sql2008relnotetools sql2008relnote kberrmsg kbtshoot kbprb KB955726
Feedback