Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. The syspolicy_purge_history SQL Server Agent job may fail in SQL Server 2008

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.

↑ Back to the top

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.

↑ Back to the top

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

↑ Back to the top

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.

↑ Back to the top

Keywords: kb, kbprb, kbtshoot, kbentirenet, kberrmsg, sql2008relnote, sql2008relnotetools

↑ Back to the top

Article Info
Article ID : 955726
Revision : 5
Created on : 8/19/2020
Published on : 8/20/2020
Exists online : False
Views : 357