Cannot access Agent Jobs when SQL Service is restarted

Today the SQL Services on one of the production servers went down unexpectedly. This instance used to generate memory dump files intermittently but today it brought down the instance. However, we already have case opened with Microsoft Support on this issue. Expecting them to come back with a fix very soon.

During this episode, observed one interesting fact. Since the services were down, they were started manually. SQL Server and SQL Agent services were running fine. As part of root cause analysis, Microsoft Support had asked us to run an agent job which captures OLEDB related events in a table.

Since the SQL Agent terminated abruptly first thing I remembered was to start this job, since I did not want to miss out on any information which might lead to a permanent fix for this issue. So fired up SSMS and navigated to SQL Server Agent and see what? Agent is disabled !

Not exactly the SQL Server Agent was disabled but the “Agent XPs” were. The SQL Server Agent was running and there is no way anyone would have disabled it. Pinching myself, headed back to the Services applet to confirm if the SQL Server Agent service was running.

I was right, the SQL Server Agent was running. How could the SQL Server Agent be disabled? Then Pradeep remembered that the SQL Services had stopped abruptly ! The best place to look for is the SQL Server Error Log.

The last entry in the error log read

****************

Recovery of database ‘MyDatabase’ (7) is 3% complete (approximately 1016 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.

****************

Here lies the resolution! How? The SQLAGENT.OUT file will make it more clear.

****************

2010-08-12 05:19:05 – ? [393] Waiting for SQL Server to recover databases…

****************

The mystery will unfold once the Recovery of this database is complete. Because SQL Server Agent will not start functioning until the attempt to recover all  the databases is complete. Once the recovery for this database was complete, the SQL Server Error had this information.

***************

8/12/2010 5:29:38 AM – spid5s – 15 transactions rolled back in database ‘MyDatabase’ (7). This is an informational message only. No user action is required.
8/12/2010 5:29:38 AM – spid5s – Recovery is writing a checkpoint in database ‘MyDatabase’ (7). This is an informational message only. No user action is required.
8/12/2010 5:29:39 AM – spid5s – Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.
8/12/2010 5:29:39 AM – spid5s – Recovery is complete. This is an informational message only. No user action is required.
8/12/2010 5:29:40 AM – spid52 – Configuration option ‘Agent XPs’ changed from 0 to 1. Run the RECONFIGURE statement to install.

***************

After the recovery is complete, the “Agent XPs” were enabled and as expected I started the SQL Agent job.

3 thoughts on “Cannot access Agent Jobs when SQL Service is restarted

  1. Bob

    This is a known issue and Microsoft is just not concerned. I opened a support case with them some time ago and blogged about it (2008 !), here was the response:

    http://www.lifeasbob.com/2008/02/06/SQLAgentWillNotStartWhenAUserDatabaseIsInRecovery.aspx

    RESPONSE FROM MICROSOFT (Names removed)>

    From: [nameremoved@microsoft.com]
    Sent: Friday, February 08, 2008 9:02 AM
    To: Horkay, Robert
    Cc:
    Subject: RE: Case number SRX080206600293 – SQLAgent will not start on a cluser – Open

    Hi Bob,

    We have decided to file a design change request to see if our development team will build in functionality to SQL Agent so that a registry key, trace flag or some startup parameter can be added to have SQL Agent go ahead and start up while databases are in recovery. If this is approved then a knowledge base article would be published describing how to do this and the caveats involved. Instead of taking SQL Server down to stop recovery we would only have to take SQL Agent offline to add the required parameters to change the code path in SQL Agent and let it start up immediately or at least 5 minutes after starting the service. I will continue to work on the design change request and hopefully it will be approved.

    Thank you,

    {removed}, MCDBA

    Microsoft SQL Server Support Escalation Engineer

    SQL Server Escalation Services Team

    Product Support Services (PSS) – Charlotte, NC

    1. PradeepAdiga Post author

      Thanks Bob for your response and for sharing the information! Since I stumbled on this yet again yesterday, posted it on my blog.

  2. Pingback: SQL Server Agent failing to start with the error “StartServiceCtrlDispatcher failed (error 6)” - SQL Server - SQL Server - Toad World

Comments are closed.