syspolicy_purge_history failing at step 3

Question

syspolicy_purge_history failing at step 3

chumphrey 12211

Ten Centuries

Points: 1223
More actions
January 29, 2016 at 2:22 pm

#324455

Hello,
I have a SQL Server 2012 Developer SP2 (64-bit) server where the syspolicy_purge_history job is failing at step 3 (Erase Phantom System Health Records) with the below error message:
Message
Executed as user: DOMAIN\xxx. 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 'import-module SQLPS -DisableNameChecking'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The specified module 'SQLPS' was not loaded because no valid module file was found in any module directory. ' A job step received an error at line 2 in a PowerShell script. The corresponding line is '(Get-Item SQLSERVER:\SQLPolicy\SERVER_XXX$a).EraseSystemHealthPhantomRecords()'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot find drive. A drive with the name 'SQLSERVER' does not exist. ' A job step received an error at line 2 in a PowerShell script. The corresponding line is '(Get-Item SQLSERVER:\SQLPolicy\SERVER_XXX$a).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.
The user has sysadmin permissions and is a local admin. I have googled for similar issues and tried the following with no affect:
1) ran SELECT * FROM msdb.dbo.syssubsystems WHERE start_entry_point ='PowerShellStart' and verified that SQLPS.exe is located where reported
2) dropped and recreated the Job using EXEC msdb.dbo.sp_syspolicy_create_purge_job
3) changed line 2 to specify "\DEFAULT" -
"(Get-Item SQLSERVER:\SQLPolicy\SERVER_XXX\DEFAULT).EraseSystemHealthPhantomRecords()"
This is the only server I am having this issue on. It is a default instance.
Does anyone have any suggestions to correct the error?
Thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

JeremyE SSCoach Points: 16121 More actions · Answer 1

I've seen this happen when SQL is installed on a server and then the server is renamed. Even if you run sp_dropserver and sp_addserver, the syspolicy_purge_history job is not updated and tries to connect to the old server name. Make sure the server name is correct in the job step. You can find the server name by running the following in the query window.

SELECT @@SERVERNAME

chumphrey 12211 Ten Centuries Points: 1223 More actions · Answer 2

Thanks for the post! I just verified the server name is correct and matches the server name in the error message.

akg.vimal SSC Journeyman Points: 84 More actions · Answer 3

I was getting the same error on my SQL server 2012 and finally were able to fix it. There are a lot of step involved in this fix which I have documented at below link.

Hope this works for you.

http://accidentalsqldba.blogspot.com/2019/12/syspolicypurgehistory-sql-agent-job-is.html