SQL Server Performance

SQL server agent error and Problem with Database recovery

Discussion in 'SQL Server 2005 General DBA Questions' started by viksar, Sep 2, 2007.

  1. viksar New Member

    We have SQL server 2005 with SP1 on windows 2003 server.
    Just today we are having issues with this SQL server. There are 2 issues
    First: - Though SQL server and agent started successfully, I have even checked this in event viewer, however whenever we start any job it comes with below mentioned error message. Also in management studio I can see SQL SERVER agent with Red color arrow facing down and with message "Agent XPs disabled". I can't see any logs or any sql server jobs. sql server error logs also states this informational message "Recovery of database 'StageDB' (15) is 18% complete (approximately 16233 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required."
    Msg 14258, Level 16, State 1, Procedure sp_is_sqlagent_starting, Line 9
    Cannot perform this operation while SQLServerAgent is starting. Try again later.
    Second: - There is one database on this sql server which I can access and even see objects within this database however when I check for event viewer, its showing informational mesage for this database as "Recovery of database 'StageDB' (15) is 18% complete (approximately 16233 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required."
    Now I dont know what sql server is trying to recover, may be its rolled forward transactions, how can I stop this or see whats happening.
    Also why I am not able to execute jobs and what does above sql server agent error message means?? what do I need to do??
    Any help would be appreciated.
    thanks
  2. ghemant Moderator

  3. viksar New Member

    All this article is saying is to restart sqlserver agent which I have already done couple of times. Is there any other option why this error was there and how to fix this?? restart doesn't fix this at all.
    How can I check for "auto close" option when database is in recovery mode??
  4. satya Moderator

    Check the status of the service from Administrative Tools - is the service Running or Starting?
    Also check whether the defined password for that SQLAGent service account is right.
    For AUTO CLOSE open the properties of the database and see whether 'tick' mark on AUTOCLOSE is enabled or not.
  5. viksar New Member

    Service status is Running and service account and password are correct. we have a standard account for all server services and this account is working for all other server, so there is nothing wrong with account. also I have checked eventviewer and sql server logs and sql server agent has started up suceesfully.
    "Auto close" option is false for the database??
    can you please suggest any more options???
    Thanks
  6. satya Moderator

    You might test the fixing of NT fibrest in server properties as per the KBA http://support.microsoft.com/?id=303287 link.
    Also look at the SQLAgent log in this case for more information,as it writes there.
  7. Raghu New Member

    Hi Viksar,
    I have also faced the same issue..
    SQL Server agent will not be accessible untill all your database has recovered after recycle / reboot.
    If you could see the error log you can find the information about the current database recovery percentage.
    Thanks
    Raghu
  8. satya Moderator

    SQLAgent is not dependant on user databases, so you have to consider about MSDB &master only.
  9. Raghu New Member

    Hi Satya,
    Yes, SQL agent is dependent only on Master & model only.
    But After SQL server recycle the agent will be up but it will accessible untill all the database has recovered. If any database recovery is incomplete the agent will not be accsible.
    I have faced the same issue in my production database.
    We will not face this issue if the SQL server is online if any database went into suspect state, the agent will be still accessible. But only on recycle, the agent will accessible only after the database has recovered.
    if i find any article i will let you know
  10. satya Moderator

    That puzzles me, within my environment that is not the case in any of the SQl 2005 instances.
    Are you using FIbre mode option
  11. viksar New Member

    Hi All,
    Thanks for your invaluable information. Our server is not on fibre mode. And yes Raghu is right, after sql server restart, it also waits for user databases to be up and not just system databases. please see attached messages below from sql server logs and sql server agent logs.
    I have resolved this by Changing "AGENT XPs value" to 1 and running reconfigure statement to refresh the value. After this I stopped SQL server, moved the database with issues to different location and restarted SQL server. Then I deleted entry of that suspected database fro sysdatabases and finally recovered this database again from backup. After that everything seems to be running fine since yesterday.
    Message from SQL Server Logs
    Source spid4s
    Message
    Recovery of database 'StageDB' (15) is 7% complete (approximately 31602 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
    Message from sql server agent
    2007-09-04 09:34:13 - ? [393] Waiting for SQL Server to recover databases...
  12. daspeac New Member

    Hi there, you may also try the sql 2005 in recovery program, it automates the procedure of database recovery. Hope it helps despite the fact that it is late, maybe it can help others
  13. rohit2900 Member

    What is the size of the stagedb database and for how long you're seeing this message???

Share This Page