syspolicy_purge_history failing

Question

syspolicy_purge_history failing

Peter Vramby

Default port

Points: 1439
More actions
January 11, 2012 at 2:13 am

#262690

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:
Date1/11/2012 10:04:53 AM
LogSQL 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
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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Perry Whittle SSC Guru Points: 234013 More actions · Answer 1

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:
Date1/11/2012 10:04:53 AM
LogSQL 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
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

edit the job step that fails (should be the erase step) and in the command section replace the computername with the virtualserver name in the command line, should run fine after that.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Peter Vramby Default port Points: 1439 More actions · Answer 2

Yes, changing the name from the node name to the virtual instance name was necessary in SQL 2005, but in SQL 2008 it directly fills in the correct name. Here's the content of the "Erase Phantom System Health Records." powershell step:

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

I changed the actual servername to <Logical server name> in the example.

Still no improvement.

Greetings,

Peter

Chuck Hottle SSCarpal Tunnel Points: 4777 More actions · Answer 3

What's the current status of the job in the activity monitor?

Peter Vramby Default port Points: 1439 More actions · Answer 4

The status in the Job Activity Monitor is also "Suspended". But when I take the properties of the job it says that the job is enabled.

Peter Vramby Default port Points: 1439 More actions · Answer 5

Finally found the answer. It seemed that the path to the SQLPS.exe file was not correct.

I followed the instructions at the following page and that solved my case

http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/8d4f0776-7086-4f82-bceb-52286e4a9842

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.

I am just posting the answer so that it can be helpfull to others having the same issue.

Greetings,

Peter

sankartosms Valued Member Points: 51 More actions · Answer 6

Hi Peter,

I am also getting same error.i tried to run that query but no result came,after search that file "SQLPS.exe" path but result was no record found.

Please give the solution for this issue.

Thanks,

P.SANKAR