Date 1/11/2012 10:04:53 AM
Log SQL Server Agent (Current - 1/11/2012 9:56:00 AM)

Message
[000] Request to run job syspolicy_purge_history (from User domain\myuser) refused because the job has been suspended

peter Vramby (1/11/2012)

---

Hello,

I have a clustered (active-passive) SQL Server cluster running under version 10.0.4000.0 (SQL 2008 SP2 64-bit Enterprise Edition) where the syspolicy_purge_history job is failing.

When I try to run it manually, it pops up an error message telling that job has been suspended (error: 22022)
When I take the history of the job, I can see that no logs were being added to it.
When I loo into the SQL agent error log, I see the following message:

But I never did suspend the job and don't know how to "un-suspend" it.

As this job is an auto created job during the installation of SQL server, I don't know what is causing the problem.

Does any of you have the same problem or experienced the same thing and has a solution for avoiding this problem?

Greetings,
Peter

(Get-Item SQLSERVER:\SQLPolicy\<Logical server name>\SQLDB1).EraseSystemHealthPhantomRecords()

The error indicates the invalid location of SQLPS.exe file. To troubleshoot the issue, please follow the below steps:

1. Execute the following statements to check the location of SQLPS.exe file.

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


2. Verify the ‘SQLPS.exe’ file is located in the path as per step 1.
3. If you cannot find the SQLPS.exe in the path above, search for SQLPS.exe on the server and correct the path recorded in the msdb.dbo.syssubsystems table by using the following statements:

Use msdb;
GO
sp_configure 'allow updates', 1 ;
RECONFIGURE WITH OVERRIDE ;
GO
UPDATE msdb.dbo.syssubsystems SET agent_exe='<full_path>' WHERE start_entry_point ='PowerShellStart';
GO
sp_configure 'allow updates', 0;
RECONFIGURE WITH OVERRIDE ;
GO


4. Confirm that SQLPS.exe file path has changed by running the below script once again:

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


5. Restart SQL Server Agent Service.