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’.

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

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.

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

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

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

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)