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

Article translations Article translations
Article ID: 955726 - View products that this article applies to.
Expand all | Collapse all

On This Page

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 PurgeHistory
SELECT @jobId = CAST(current_value AS uniqueidentifier)
FROM msdb.dbo.syspolicy_configuration_internal
WHERE name = N'PurgeHistoryJobGuid'

-- Delete the job identifier association in the syspolicy configuration

DELETE FROM msdb.dbo.syspolicy_configuration_internal
WHERE name = N'PurgeHistoryJobGuid'

-- Delete the offending job
EXEC msdb.dbo.sp_delete_job @job_id = @jobId

-- Re-create the job and its association in the syspolicy configuration table
EXEC 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.

Properties

Article ID: 955726 - Last Review: March 19, 2009 - Revision: 2.0
APPLIES TO
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Workgroup
  • Microsoft SQL Server 2008 Developer
Keywords: 
sql2008relnotetools sql2008relnote kberrmsg kbtshoot kbprb KB955726

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com