Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2008
    Posts
    19

    Unanswered: Database stuck In Recovery

    So due to hardware/network issues we have a database that is stuck "In Recovery" mode for an extended period of time.
    The SQL log files start to process the recovery and it gets to 12% recovered specifying that it has 1 transaction to roll forward but stays there for hours and hours.
    It appears we are at this situation due to an unintended quick shutdown of this DB

    Stats:
    Yes there is not a recent backup, (I was called into this one after the fact)
    The Db is relatively small (~500 MB)
    Recovery mode is set to Simple
    SQL 2008 RTM
    I don't care about loosing any data on this DB I just want it restored with the SP's, Views, table structures, etc.
    There are other Db's on the server that are critical and still in good working conditions with proper backups

    What I have tried:
    Try to set the DB to Emergency mode but it won't let me.

    Stop SQL, rename the log file to see if SQL re-creates one for me but it doesn't because it doesn't think the MDF was shut down or detached correctly which is true

    Take a very old backup, detach it from a working SQL instance, take that log file and swap it in for the bad log file to try and fool the MDF but it knows they are not paired correctly.

    Try to alter database with reattach_rebuild_log but this ran for quite a while too.

    So...
    Any other ideas?
    Since I don't care about the transactions in that log file how can I get it to start and ignore whatever it is trying to roll forward?

    Thanks for th help!

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Waht commands are you using to set the database to EMERGENCY state? What error messages are you getting when trying those commands? There used to be 3 ways to recover a database with an MDF available in 2K: (1) Detach database, create database ...for attach by specifying the data device only; (2) DBCC REBUILD_LOG(...); (3) Shut down the instance, Delete/Rename the log file, Restart the instance (the log would be rebuilt automatically). Which ones have you tried?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jun 2008
    Posts
    19
    Quote Originally Posted by rdjabarov View Post
    Waht commands are you using to set the database to EMERGENCY state? What error messages are you getting when trying those commands? There used to be 3 ways to recover a database with an MDF available in 2K: (1) Detach database, create database ...for attach by specifying the data device only; (2) DBCC REBUILD_LOG(...); (3) Shut down the instance, Delete/Rename the log file, Restart the instance (the log would be rebuilt automatically). Which ones have you tried?
    When I try to set the database into Emergency I get this error. Presumably because the DB state is still in recovery.
    Msg 5011, Level 14, State 7, Line 1
    User does not have permission to alter database 'DBName', the database does not exist, or the database is not in a state that allows access checks.
    Msg 5069, Level 16, State 1, Line 1
    ALTER DATABASE statement failed.


    I can't detach the database OR execute any SQL statements against it because the restore process has a lock on the db so any statements are prevented from execution.
    If I shut down the instance and delete the log file, then try to restart it the MDF file is not even recognized
    Last edited by DBAH; 02-06-11 at 23:20.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    There are two phases to recovery. Roll forward (completed) and roll back (which is presumably running). How big is the log for this database? If all you need is the general structure, a month-old database backup should be fine.

  5. #5
    Join Date
    Jun 2008
    Posts
    19
    Quote Originally Posted by MCrowley View Post
    There are two phases to recovery. Roll forward (completed) and roll back (which is presumably running). How big is the log for this database? If all you need is the general structure, a month-old database backup should be fine.
    Based on the SQL log it says there is 1 transaction to be rolled forward so that is what I think it is doing.
    The log is approx 230 MB.

    There is no backup.

    Is it possible to kill the SPID that is doing the recovery and still gain access to the DB knowing that the data will be inaccuate?

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    The recovery SPID is likely going to be a system SPID, so you may not be able to kill it. Honestly, I have never tried, so I am not sure what would happen. I expect the command will bounce with an error, but then, you might clear the locks on the database. Before doing that, I would probably see if there is any willingness from the business to pay for a call to Microsoft.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •