January 11, 2012 at 2:13 am
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:
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
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
January 11, 2012 at 3:26 am
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:
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
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" 😉
January 11, 2012 at 4:23 am
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
January 11, 2012 at 2:50 pm
What's the current status of the job in the activity monitor?
January 12, 2012 at 8:41 am
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.
January 12, 2012 at 8:59 am
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
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
March 19, 2014 at 8:59 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply