SQL Server Performance

Jobs running forever after SP2

Discussion in 'SQL Server 2005 General DBA Questions' started by satya.sqldba, May 8, 2007.

  1. satya.sqldba New Member

    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
  2. satya Moderator

    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.
  3. satya.sqldba New Member

    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
  4. satya Moderator

    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.
  5. satya.sqldba New Member

    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
  6. satya Moderator

    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.
  7. satya.sqldba New Member

    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.

  8. satya Moderator

    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>
  9. satya.sqldba New Member

    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


  10. satya Moderator

    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.
  11. satya.sqldba New Member

    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
  12. satya Moderator

    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.
  13. satya.sqldba New Member

    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
  14. satya Moderator

    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.
  15. satya.sqldba New Member

    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
  16. satya.sqldba New Member

    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
  17. MohammedU New Member

    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.
  18. satya.sqldba New Member

    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

  19. MohammedU New Member

    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.
  20. satya Moderator

    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.
  21. satya Moderator

    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.
  22. satya.sqldba New Member

    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
  23. satya.sqldba New Member

    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
  24. satya Moderator

    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.
  25. MohammedU New Member

    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.
  26. satya.sqldba New Member

    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
  27. MohammedU New Member

    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.
  28. satya Moderator

    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.
  29. satya.sqldba New Member

    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
  30. satya Moderator

    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.

Share This Page