Sunday, June 24, 2012

'syspolicy_purge_history' cannot be run because the powershell subsystem failed to load

We may face the below error while running few jobs which has powershell script associated to any of the job steps.

Error Message
'syspolicy_purge_history' cannot be run because the powershell subsystem failed to load

We can fix the issue by updating the value in one of the system table in MSDB database.
syssubsystems - It contains information about all available SQL Server agent proxy subsystems. It is stored under MSDB database.

1) Use msdb
select * from syssubsystems
2) Verify the record of powershell subsystem, need to verify two things
  a) subsystem_dll  b) agent_exe
If there is any mismatch in the dll path or exe paths, then we need to update the entries with accurate paths.
3) This is a system table, so we need to run the below command to allow updates into system tables.
EXEC sp_configure 'allow updates', 1;
reconfigure with override;
Prepare the update command / delete complete information the table.
4) To Re-Populate
exec msdb.dbo.sp_verify_subsystems 1 / Restart the SQL Server Agent Service
5) Disallow updates
EXEC sp_configure 'allow updates', 0;
reconfigure with override;

No comments: