As a SQL Server consultant, I often come across various challenges and issues faced by my clients. Recently, one of my clients approached me with an error message related to the syspolicy_purge_history job. This job is commonly found in SQL Server instances from SQL Server 2008 onwards and is related to the Policy Based Management (PBM) feature introduced in SQL Server 2008.

Policy Based Management allows you to define and enforce policies on your SQL Server instances. These policies can be conditions such as disabling xp_cmdshell or ensuring that auto-shrink is turned off for databases. When a policy runs, the results are stored in the MSDB database. Over time, this can lead to unnecessary data accumulation in the MSDB database. To address this, the syspolicy_purge_history job is responsible for cleaning up data older than the number of days defined in the HistoryRetentionInDays property of Policy Management.

Now, let’s dive into the specific problem my client encountered. They reported that the third step of the syspolicy_purge_history job was failing. The job history provided the following output:

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.

Upon analyzing the error message, it became clear that the issue was related to PowerShell on the server. The PowerShell script in the job was failing at the first line, which was attempting to import the SQLPS module. I decided to test the command in PowerShell and encountered the same error message.

To further investigate, I used Process Monitor, a tool I find extremely helpful in troubleshooting. Through Process Monitor, I discovered that PowerShell was looking for the PSModulePath variable, which was pointing to a non-existent D drive on the client’s machine. The PSModulePath variable is responsible for locating the SQLPS folder, which contains the necessary modules for SQL Server.

To resolve this issue, we modified the PSModulePath environment variable to point to the correct location on the E drive, where the SQL Server tools were installed. This allowed PowerShell to locate the SQLPS folder and successfully execute the script.

If you ever encounter a similar problem with the syspolicy_purge_history job or any other PowerShell-related issues, you can follow these steps to modify the PSModulePath environment variable:

1. Right-click on “My Computer” and select “Properties”.
2. Go to the “Advanced” tab.
3. Click on “Environment Variables”.
4. Locate the “PSModulePath” variable and click “Edit”.
5. Ensure that the path includes the location of the SQLPS folder.

By making this adjustment, you can ensure that PowerShell can locate the necessary modules and successfully execute scripts related to SQL Server.

As a consultant, this experience served as a valuable learning opportunity for me. It highlighted the importance of troubleshooting and utilizing tools like Process Monitor to identify and resolve seemingly simple issues. If you ever come across a similar problem in your environment, I hope this article helps you troubleshoot and resolve it effectively.

Feel free to share your experiences or any other SQL Server-related challenges you’ve encountered in the comments below.