Thursday, July 21, 2016

"syspolicy_purge_history" Job is failing with error "PowerShell subsystem failed to load"

Sometimes the SQL server Job syspolicy_purge_history will fail with the below error 

"Unable to start execution of step 3 (reason: The PowerShell subsystem failed to load [see the SQLAGENT.OUT file for details]; The job has been suspended).  The step failed." 

The Cause of this failure is the invalid location of SQLPS.exe file. We can check the current path SQL server is using for SQLPS.exe using below query:

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

We can go to above location and can check SQLPS.exe exists or not.If not we can simply search the SQLPS.exe in our system and can copy it to above location.

Or we can update the location of it.

Use msdb
sp_configure 'allow updates', 1

--If the path is "C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\SQLPS.exe"

UPDATE msdb.dbo.syssubsystems SET agent_exe='C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\SQLPS.exe' WHERE start_entry_point ='PowerShellStart'

sp_configure 'allow updates', 0

Now restart the SQL server agent and try again to run the job.

Thank You!!