syspolicy_purge_history - PowerShell subsystem failed to load Error

Error Message: 

Step 3 of job 'syspolicy_purge_history' (0x92D690B2954DA942915A08224A906C93) cannot be run because the PowerShell subsystem failed to load.  The job has been suspended.

Cause:  Invalid location of SQLPS.exe file

Steps to fix:

1.     Search the location of SQLPS.exe file by using the following quey

SELECT * FROM msdb.dbo.syssubsystems WHERE start_entry_point ='PowerShellStart'

   

     2.     Go to the server and check whether the file ‘SQLPS.exe’ is located in the path as per step 1.

     3.      If the paths are different, enable 'allow updates' using the below script to be able to write into system databases. If they are the same go to step 6.

sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE 

    4.      Update the correct path.

UPDATE msdb.dbo.syssubsystems SET agent_exe='Enter Correct Full Path Here\SQLPS.exe' WHERE start_entry_point ='PowerShellStart'

    5.     Disable 'allow updates'

sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE 

   6.    Get the SQL Server name you are working on by running:  SELECT @@SERVERNAME

   7.    Go to the job properties and edit the 'Erase Phantom System Health Records' step.

Insert the value gathered from step 6.

8.    Restart SQL Agent Service.

9.    Re-run syspolicy_purge_history job.

syspolicy_purge_history - What is it?

When you do a Sql Server 2008 installation or upgrade from a previous version, a SQL Agent job will be created during the installation/upgrade process: syspolicy_purge_history.

With SQL Server 2008 a new feature “Policy Management” was introduced. When you define a policy, this can be evaluated against targets to check for compliance. Each evaluation of compliance will be recorded in tables in msdb database:

• syspolicy_policy_execution_history_details_internal
• syspolicy_policy_execution_history_internal
• syspolicy_policy_category_subscriptions_internal

The purpose of syspolicy_purge_history job is to remove records in those tables and keep them under reasonable size.