SQL SERVER – syspolicy_purge_history job failing step: Erase Phantom System Health Records

Getting back to consulting mode has got my newer clients and bigger challenges. Not only that I am seeing interests for some of the greatest and latest versions, but some clients are sitting on an older version of SQL Server requiring some serious help for trivial issues. One of my clients has asked to consult for a quick error message troubleshooting in the recent past. Since it was an interesting one about syspolicy_purge_history job, I am sharing it via the blog.

You might have seen this job syspolicy_purge_history in almost all SQL instances of SQL Server 2008 onwards. Let’s understand what is this job under SQL Server Agent. If you recall, SQL Server 2008 has introduced a new feature called Policy Based Management (PBM). A Policy could be any condition you want to check like xp_cmdshell should not be enabled on an instance, or database should not have auto-shrink ON. Whenever a policy runs the results are stored in the MSDB database. Over a period of time, this may will cause unnecessary data getting piled up in MSDB. So, the job syspolicy_purge_history cleans up the data older than the days defined in HistoryRetentionInDays property of Policy Management.

Now, let us look at the problem which my client reported. He said that the third step of the job is failing. Here is the output from job history.

Date 05-Mar-16 8:54:52 PM
Log Job History (syspolicy_purge_history)
Step ID 3
Server SQLBIG\SQL2014
Job Name syspolicy_purge_history
Step Name Erase Phantom System Health Records.
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: domain\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 ‘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\SQLBIG\SQL2014$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\SQLBIG\SQL2014$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.

If we look at the message, here is the first error

The corresponding line is ‘import-module SQLPS  -DisableNameChecking’.

Solarwinds

So I went to PowerShell prompt and executed the same command which was failing.

SQL SERVER - syspolicy_purge_history job failing step: Erase Phantom System Health Records syspurge-01

If we read the message, it’s the same message which is seen in the job history. This means that we have issues with PowerShell on this server and due to that PowerShell script is failing.

Attempt to launch SQLPS was also failing with the same error.

SQL SERVER - syspolicy_purge_history job failing step: Erase Phantom System Health Records syspurge-02

I captured my favorite tool Process Monitor to see what it is doing.  I found that it is looking for PSModulePATH variable which was below on my client’s machine,

PSModulePath=C:\WINDOWS\system32\WindowsPowerShell\v1.0\Modules\;d:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\;d:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\

When I checked, I found that there was no D drive on the system. So we modified this environment variable to point to E drive, where SQL tools were installed. Basically, we need to point to a location where we have SQLPS folder.

Here is the content of a folder

SQL SERVER - syspolicy_purge_history job failing step: Erase Phantom System Health Records syspurge-03

To change environment variables, use My computer > right click > Properties and go to “Advanced” tab as shown below:

SQL SERVER - syspolicy_purge_history job failing step: Erase Phantom System Health Records syspurge-04

Over there, click on “Environment Variables” and choose PSModulePath and Edit it.

SQL SERVER - syspolicy_purge_history job failing step: Erase Phantom System Health Records syspurge-05

Make sure that the path shown there contains SQLPS.

Once we changed the path to right value, we were able to launch SQLPS and also run the job. Personally, I felt this was a great learning for me too, as part of troubleshooting and how tools have come to help something that looks really simple. Do let me know if you ran into this problem ever in your environment.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQL SERVER – Setup Closed with Exit Code 0x80004005
Next Post
SQL SERVER – The NOLOCK Question – Notes from the Field #117

Related Posts

13 Comments. Leave new

  • Hi Pinal,

    Step3 in Syspurge job fails with the below error after enabling TLS1.2 in the server. Kindly advise.

    Message
    Executed as user: NT Service\SQLSERVERAGENT. A job step received an error at line 2 in a PowerShell script. The corresponding line is ‘(Get-Item SQLSERVER:\SQLPolicy\Servername$a).EraseSystemHealthPhantomRecords()’. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘Failed to connect to server . A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.) ‘. Process Exit Code -1. The step failed.

    Reply
  • Great post, and huge help!
    Although my problem was slightly different, your article got me in the right path.
    You get very similar error if you install SSMS 2016 with SQL2012 (as it is now a separate package).

    The problem we encountered was very similar, and related to SQLPS as well: “Could not load assembly because this assembly is built by a runtime newer than the currently loaded runtime”.

    So after reading your article, I found out that the folders are listed in the environment variable as follows:
    programfiles\Microsoft SQL Server\130\Tools\PowerShell\Modules\;
    programfiles\Microsoft SQL Server\110\Tools\PowerShell\Modules\

    Obviously, SSMS2016 created the 130 folder, and SQL Server the 110.
    Now, when the job runs, it is searching in the 130 first, where it will indeed find an SQLPS folder, only the wrong one, and it will fail to complete with it.

    To overcome this problem, all you need to do is to just swap the two paths in the variable, so that the job would search in the correct folder.

    Reply
  • Once again, your blog has helped me solve a problem. Thank you!

    Reply
  • Mamuka Kupatadze
    April 3, 2018 6:31 pm

    Tanks it works

    Reply
  • Had the exact same problem – your fix worked great. Thanks!

    Reply
  • Excellent article. However, if you change the environment variable for just this issue, then other issues could arise. I simply copied the SQLPS folder from the default SQL location to the Windows Powershell location. I was able to successfully run the sys_poicy_purge_history job. Be careful of changing environment variables less you break other things running on the server.

    Reply
  • I did the same by simply just copying the SQLPS folder to the default location where PSModulePath was (%SystemRoot%\system32\WindowsPowerShell\v1.0\Modules\) and it fixed the issue

    Reply
  • Hi,
    I am using 2014 & getting following error in step 3 “Erase Phantom System Health Records.”:

    Executed as user: user_name. The step did not generate any output. Process Exit Code 255. The step failed.

    Note: job is being executed successfully more than a year.

    Any help, Thanks

    Reply

Leave a Reply

Menu