none
SQL 2008 R2 Database stuck in Restoring... state after attempting a Full Restore

Answers

  • So, even though the Execution of the Restore was 100%, it seems to have been still working on it? Something seems amiss to me. Bad progress indicator?


    IT is like a box of chocolates, you never know what you might bite into.

    Though the restore process might say its completed 100% , the time to recover it completely or start up the database depends on number of virtual log files which can be of a reason for the behavior you are seeing.  The percent completed you see on a restore screen is only for file initialization and data copy phase , once the data copy has completed into the containers the database start-up process will go into redo and undo queues to get  it online with a consistent state. This time taken to start up any databases process has a direct impact on number of VLFs , not only during a database restore process you will see this but every time your database starts up

    You can perhaps use the trace flags 3014,3604\3605 to see the operations or steps your restore is going through


    Thanks, Leks


    Saturday, March 24, 2012 1:09 AM
  • Update: We were finally able to get on the phone with the vendor and they have confirmed that the database is back to where it should be.

    So, it was a 2 1/2 hour restore of this database.

    About the continuous failed logins, every second; I found that in fact the application server was never fully shutdown, there was a process associated to the application that was never fully stopped. Which caused all of the failed login attempts against this DB.

    @sqlrockz; I want to express my appreciation to you for your time and assistance. Thank you! Just having someone answer the call for help was refreshing. It seemed on a Friday, all of the other help channels were difficult to access.


    IT is like a box of chocolates, you never know what you might bite into.

    • Marked as answer by VenturaCC Friday, March 23, 2012 9:23 PM
    Friday, March 23, 2012 9:21 PM

All replies

  • What error it gives you when you try to run the following command ?

    RESTORE DATABASE xxx

    WITH RECOVERY



    • Edited by sqlrockz Friday, March 23, 2012 6:43 PM
    Friday, March 23, 2012 6:40 PM
  • @sqlrockz; Thank you for your quick response.

    Msg 3101, Level 16, State 1, Line 1
    Exclusive access could not be obtained because the database is in use.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

    * And, though I'm sure the "in use" is caused by the stuck Restoring state, I thought I might mention that I'm being told the application server is in a "downed" state. But, the SQL Server log is getting filled with Login failure messages from the application user account. Weird. We also have the vendor involved and we're waiting on a response from their high level support guys.

    Also, from the log: The database "x" is marked RESTORING and is in a state that does not allow recovery to be run. Just before this is: Starting up database 'x'.


    IT is like a box of chocolates, you never know what you might bite into.



    • Edited by VenturaCC Friday, March 23, 2012 7:06 PM
    Friday, March 23, 2012 6:49 PM
  • What TSQL command did you used to restore this db ? did you mentioned NORECOVERY option when restoring ?

    Also could you run this query and post the output

    select * from sys.sysprocesses
    where dbid = DB_ID ('xxx')

    Friday, March 23, 2012 7:12 PM
  • It was a restore ... replace, recovery.

    Updated status: I went to lunch, checked in while I was eating and the (Restoring state is no longer showing.

    Something must have went well.

    So, even though the Execution of the Restore was 100%, it seems to have been still working on it? Something seems amiss to me. Bad progress indicator?


    IT is like a box of chocolates, you never know what you might bite into.

    Friday, March 23, 2012 8:04 PM
  • Update: We were finally able to get on the phone with the vendor and they have confirmed that the database is back to where it should be.

    So, it was a 2 1/2 hour restore of this database.

    About the continuous failed logins, every second; I found that in fact the application server was never fully shutdown, there was a process associated to the application that was never fully stopped. Which caused all of the failed login attempts against this DB.

    @sqlrockz; I want to express my appreciation to you for your time and assistance. Thank you! Just having someone answer the call for help was refreshing. It seemed on a Friday, all of the other help channels were difficult to access.


    IT is like a box of chocolates, you never know what you might bite into.

    • Marked as answer by VenturaCC Friday, March 23, 2012 9:23 PM
    Friday, March 23, 2012 9:21 PM
  • So, even though the Execution of the Restore was 100%, it seems to have been still working on it? Something seems amiss to me. Bad progress indicator?


    IT is like a box of chocolates, you never know what you might bite into.

    Though the restore process might say its completed 100% , the time to recover it completely or start up the database depends on number of virtual log files which can be of a reason for the behavior you are seeing.  The percent completed you see on a restore screen is only for file initialization and data copy phase , once the data copy has completed into the containers the database start-up process will go into redo and undo queues to get  it online with a consistent state. This time taken to start up any databases process has a direct impact on number of VLFs , not only during a database restore process you will see this but every time your database starts up

    You can perhaps use the trace flags 3014,3604\3605 to see the operations or steps your restore is going through


    Thanks, Leks


    Saturday, March 24, 2012 1:09 AM
  • @Lekss - Thank you for taking the time to explain that. That is very helpful.


    IT is like a box of chocolates, you never know what you might bite into.

    Monday, March 26, 2012 3:11 PM
  • You did blunder by restarting SQL Server. You should have not done that.Unless SQL server recovers fully and comes to state where it finds itself in same consistent state as it was before the force shutdown it will show in recovery state. Your best bet is to wait and watch you cannot run any transaction on recovering database/SQL Server. Also since you manually killed SQL server process while it was rolling back a query its even more worse. Now when SQL server will come online the whole rollback process will start from beginning not from point where you killed SQL server process/query. So it might take time and unless it fully rolls back the previous killed query it won't come online neither you can force it to.

    One advantage you can have is if you have Enterprise edition SQL Server will come online after redo phase of recovery. So that might be little relief to you.

    PS: Seriously avoid any such random suggesstion given on web. You should take this as a lesson to never kill SQL server process or rest if certain query is taking time. You might have waited and checked rollback status of query using beow script

    select session_id,
    blocking_session_id,
    wait_type,
    percent_complete, 
    ((estimated_completion_time)/1000) as Estimated_Complettion_time_Sec,
    ((total_elapsed_time)/1000) Toatal_Elapsed_time_Sec 
    from sys.dm_exec_requests where status='rollback'
    Tuesday, August 12, 2014 12:05 PM