Jobs running forever after SP2 | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Jobs running forever after SP2

I recently installed SP on my server and suddenly, the jobs that have been created by the maitenance plans stopped running. When I try to execute the jobs, they are running forever. Even after 14 hrs, normal jobs like Update statistics, Integrity check still are in running state. When I issue a query for update stats, they are geting executed in hardly 1 or 2 minutes. But the jobs are running forever. I tired re-creating the jobs, but it is again the same problem [V] Thanks
Satya
Any chance you drop those maintenance plans and recreate afresh? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Ya I have already done that, by saying re-creating jobs, what I meant was I re-created the MP’s. Still same problem… Thanks
Satya
Have you refreshed the job state under SSMS or check using SP_HELP_JOBHISTORY. When they have stopped running, do you see any error or warning from SQLAgent log? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Yes Satya I have checked that and also refreshed that so many times before that. When I look at the sp_help_jobhistory, I donot see at least that the job has started at all. When I view the history of the job it shows nothing (no history that it has run). But when I look at job activity monitor, I see that the job is in running state. BEcause it has been in the same state for more than 14 hrs, I deleted the jobs and the message in error log is: Message
[180] Job Update Statistics.Subplan_1 was deleted while it was executing: the outcome was (Unknown) Thanks
Satya
I think we have tried most of the options, as a last resort you might try restart SQLAgent or check the job history table too as it will have contention for having too many rows. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
I understand what you say regarding the job history table. But I am having trouble only with jobs that have been created by MP’s. The other jobs that I have scripted are running fine.
You might try restarting SQLAgent in this case, sometimes the Microsoft products gets refreshed this way easily [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />].<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
I have done that too, same trouble once again. When I look at the job history now the error message shows: Message
The job was stopped prior to completion by Shutdown Sequence 0. The Job was invoked by Schedule 24 (Update statistics). The last step to run was step 1 (Subplan_1). After I have started executing the job, and when I look at the sysjobactivity table, it shows nothing runing, but the job activity monitor shows the job is in running state.
When I try to re-run the job it fails giving the following message: Message
[000] Request to run job Update statistics.Subplan_1 (from User sa) refused because the job is already running from a request by Schedule 24 (Update statistics)
Apparently SQL Server is not recognising that the job has started… Thanks
Satya

I;m still concerned on the number of rows in sysjobhistory system table, might check there and see whether you can purge the records. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Satya I have tried deleting about 40 rows from the sysjobhistory table and tired to execute the job once again…still no progress. When I look at the sysjobactivity, it actually shows that the job has been started, but it is not ending. I am not sure where something might be wrong. One more interesting thing is, when I go to SQL Server Agent Properties, and try to check the ‘Automatically remove agent history’ property and configured it to erase history older than 4 weeks, and hit ‘Ok’, it is reverting back to unchecked state once again when I go and view the properties of SQL Server Agent… Thanks
Satya
Can you confirm the privileges for the SQL agent account, also confirm the number of rows currently reside in that table.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Satya sqlagent is admin on this server. And the sysjobhistory has 64 rows One more interesting thing is when I issue a sp_stop_job ‘jobname’ against the never ending job, the job is stopped successfully and the job activity monitor shows that the job has been cancelled. Now the sysjobactivity indicates the columns as shown below: start_execution_date
2007-05-08 11:11:53.000 last_executed_step_date
2007-05-08 11:11:53.000 stop_excution_date
2007-05-08 12:56:07.000 (after I ran sp_stop_job)
Thanks
Satya
Just another note, have you applied SP2 for Client tools also> Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Another similar scenario I just found somewhere http://www.unixadmintalk.com/f46/sql2k5-sp2a-running-sp_updatestats-takes-forever-266861/ Though not exactly same, is my problem somhow related to the above link? Thanks
Satya
I am working on a Dev server right now and I have installed SP2 on it. I am not running any tools from other machine. @@version gives the following: Microsoft SQL Server 2005 – 9.00.2047.00 (Intel X86) Apr 14 2006 01:12:25 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2) Thanks
Satya
Your build is not looks like you are on SP2…
Run the following query and see what build and sp you are on… SELECT CONVERT(char(20), SERVERPROPERTY(‘servername’)),
CONVERT(char(20), SERVERPROPERTY(‘ProductVersion’)),
CONVERT(char(20), SERVERPROPERTY(‘ProductLevel’)) Run the sql profiler and see what is going in behind the scenes…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

Yes Mohammed I see SP1 there. I am sure I have installed SP2 on the Dev server, and the installation was smooth and it didn’t give me any problems. Apparently something went wrong during the installation… What events do you want me to monitor using SQL Profiler? Thanks
Satya
Check the installation log file in bootrap folder for details about installtion issue… In profiler monitor the Maintenance plan job activity and commands… MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

That SP2 you are referring is for Windows and 2047 for SQL is still SP1 and you would see 3042 for SP2. I don’t understand the requirement of Profiler here when updating with Service pack, as you wouldn’t be able to monitor, correct me if Im wrong. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
You might check the SQLSP.LOG file too in this case for the error caused during that SP2 patch. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Yes Satya 2047 means I still have SP1, so something went wrong during the SP2 installation in my case.But,when I hit Help—> About in SSMS I see version 3042 for SSMS and Analysis Services Client tools. The installation wasn’t completely perfect. I will try re-installing it. Thanks
Satya
Thanks for the info Satya and Mohammed
I looked at the log file, the instalation of version 3042 failed because of the following error: Error Number : 29506
Error Description : MSP Error: 29506 SQL Server Setup failed to modify security permissions on file c:program FilesMicrosoft SQL ServerMSSQL.1MSSQLData for user *******. To proceed, verify that the account and domain running SQL Server Setup exist, that the account running SQL Server Setup has administrator privileges, and that exists on the destination drive. The SQL Server is running under sqlagent account and it is already an admin on the server. I logged into the server and I am already an admin on the server. Thinking of any other resason why it went wrong…. Satya
There might be issues with the security policies, not the local policy it is the domain security policies and ensure the SQL service account has Service LogOn right on the server. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
quote:Originally posted by satya.sqldba Thanks for the info Satya and Mohammed
I looked at the log file, the instalation of version 3042 failed because of the following error: Error Number : 29506
Error Description : MSP Error: 29506 SQL Server Setup failed to modify security permissions on file c:program FilesMicrosoft SQL ServerMSSQL.1MSSQLData for user *******. To proceed, verify that the account and domain running SQL Server Setup exist, that the account running SQL Server Setup has administrator privileges, and that exists on the destination drive. The SQL Server is running under sqlagent account and it is already an admin on the server. I logged into the server and I am already an admin on the server. Thinking of any other resason why it went wrong…. Satya

It is always better to check the installation output files and build number after installation…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

I re-installed, this time I checked the bootstrap folder and made sure everything is fine Jobs problem is solved. They are running fine now. Thanks
Satya
Good… Now the question is why the jobs were not running when it was on SP1? MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

You may be aware there are few issues associated with MP & SSIS prior to SP2 and they were fixed with the latest SP2 & fix, so for this reason it is always to apply the latest service packs on all the instances where you have the MP including the client tools, to apply the update on SSIS components too. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
NO Mohammed, The jobs created by MP’s were running fine when SP1 was thr. They only failed after the Sp2 was applied in a faulty way. I think they failed only because SP2 wasn’t installed completely. Thanks
Satya
Could be one of the reasons and also if there is a mismatch of SSIS components between the instances it happens. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>