SQL Server Performance

SQL 2005 - Recovery Issues Help

Discussion in 'Getting Started' started by Emile, Dec 16, 2008.

  1. Emile New Member

    Hi all I am new to this forum so apologies in advance if I am not putting this in the right subject area. My ProblemWe have a SQL Server that recently got rebooted to allow for multiple updates to be applied. I am uncertain how long this box has been left without being rebooted however it seems like it has been a very long time. When it did finally kick back to life there were some databases still in recovery mode (why I do not know, it was a simple reboot) including msdb. I have also noticed that SQL Agent is not started. Unfortunately there are no backup files to restore from. I started up the server and get the green play button as expected on the server end. However if I try start SQL agent it goes through the process and doesn’t kick up any immediate errors, however looking at the status (circle) it appears to have a small red arrow pointing down and one above it in green pointing right. It also indicates in "Agent XPs is disabled". I can click on restart in the GUI however it doesn’t seem to make any difference. When I log onto the client machine the status of this server is a empty white circle. Running select * from sys.sysdatabases
    I’m getting a few databases coming up with status 65544If I try run any queries they just keep running and I have to force it to stop. A simple select count(*) operation which used to take a seconds never finishes. Any ideas anyone?Many Thanks
  2. satya Moderator

    Welcome to the forums.
    This is a scariest moment that you do not have the backup for this database and expecting to recover the database may not be an easy job.
    Firstly you need to see any hardward related issues reported on the system, refer to event viewer system & applicaton log for further information.
    Here is the blog SQL Server Database Corruption - a nightmare for a DBA, what are your chances to survive? that will give you more information and links to recover it.
    Coming to AgentXP issue it is nothing but SQLAgent service is not enabled,
    Start the SQL Server Agent service by:
    • using the SQL Server Configuration Manger
      located in Start -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Configuration Manager
    • Or use the Services Management Console
      located in Start -> Control Panel -> Administrative Tools -> Services
    • Or you can start the default service from the command prompt
      net start "SQL Server Agent (instance name)"
      Eg: net start "SQL Server Agent (MSSQLSERVER)"
  3. Emile New Member

    Satya ... many thanks for the reply.
    I have already Started Server Agent as you mentioned and in Services or Server Config Manager it is listing as "green play" and started status. However if I go into SQL Server Manager on the server under notification services folder, its status is different ... what appears to be a Red Arrow down with a smaller green arrow right above it, any idea what that means? Also still has (Agent XPs disabled).
    After some more reboots all but one DB has seemed to recover itsself (one I dont need). However if I try delete it, it says in use cant drop. IF you have any ideas regarding this let me know.
    Thanks for the article, I will read though it and see how I get along in the meantime.
  4. James Ryan New Member

    will it allow you to detach the database and then delete the mdf/ldf file?
  5. Emile New Member

    Hi James
    I cant detach or drop it, even dropping all user connections and then trying to drop it from outside the db eg use master then drop or detach.
    Regardless it gives the following message "Data base [db name] is being recovered. Waiting until recovery is finished"
    It never does finish though ....
  6. Elisabeth Redei New Member

    Hi,
    If you are absolutely sure you want to delete it you can set the database in emergency mode and then drop it:
    ALTER DATABASE myDB
    SET EMERGENCY
    GO
    DROP DATABASE myDB
    HTH
    /Elisabeth
  7. Emile New Member

    Hi Elisabeth
    Thanks for the idea, when I tried it I get
    "USer does not have permissions to alter database, or the database does not exist"
    Looking at my roles, the user I am logged on as on the server has sysadmin rights.
    So still stuck, but feel like im getting closer
    Thanks again for your suggestion
  8. satya Moderator

  9. Emile New Member

    Satya, thanks again for the reply
    Unfortunatley that link you provided with the solution requires that you detach the database which I cant seem to do .. so still stuck
    Many thanks for the idea though
  10. Emile New Member

    FYI ... for those of you who are having similar issues I found a great article for recovery using Emergency mode.
    http://www.sqlskills.com/blogs/paul...CY-mode-repair-the-very-very-last-resort.aspx
    Unfortunaltey I cant use it as I am getting the follwing error when running/* Set only one user on database, need to drop other users to go into emergency mode */ALTER
    DATABASE IS2005SbSDW SET SINGLE_USER;GO
    ;/* Set DB to Emergency Mode */ALTER
    DATABASE IS2005SbSDW SET EMERGENCY;Go
    ;
    "
    Msg 5061, Level 16, State 1, Line 1
    ALTER DATABASE failed because a lock could not be placed on database 'IS2005SbSDW'. Try again later.
    Msg 5069, Level 16, State 1, Line 1
    ALTER DATABASE statement failed.
    "
  11. Elisabeth Redei New Member

    Hi Emile,
    Just to avoid confusion;can you confirm that you absolutely definitely have no interest in this database whatsoever and are happy to drop it completely?
    Can you do a
    SELECT name, state, state_desc FROM sys.databases
    /Elisabeth
  12. Emile New Member

    Hi Elisabeth
    I have no issue dropping this DB it was a test db from an example book for learning purposes. Running sysdatabases all are marked as "Online" status now except for the test db which is marked with "Recovering" Status.
    As stated earlier, I can not drop or detch the database.
    Since last speaking, I moved the mdb file and ldb out of the Data folder and the status changed to an exclamation. I tried to detach but had no luck. I then attempted to take it offline and the status now shows as offline in Management Studio, however says "recovering" looking at sysdatabases.
    The only thing I can think of that was different before we rebooted the server was that the mdb and ldf for this troublesome test db was not sitting in Data. When we rebooted it was in recovery and at some point I took a copy of the mdb from where it was originally pointing and dropped it in the Data file hoping I could reattach/point it somehow. Not sure if this info is going to help at all?
    Another strange thing is I can open tables via the GUI and see results on working databases with no issues and quickly, however if I try anything with code it just gets stuck executing. I have a strong feeling this is related to the recovery states of this troublesome db and sql server agent perhaps.

Share This Page