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
    Answerer
  • 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
    Answerer
  • @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