locked
Database Restore and Offline Status? RRS feed

  • Question

  • Hi,

    Using SQL Server 2005 Standard.  I was trying to restore a backed up database but it wouldn’t restore reporting that the database was still being used.  I previously stopped the application that uses this database so I’m confident that is not the issue.  Not knowing how to determine what was holding on to the database I reasoned that if I took it offline that it would drop any connections.

    So I set it to be offline and it is taking a really long time, like hours.  Now it won’t let me open properties or anything on the database because the database is in transition.  Is there any way to cancel the offline task or tell how much time is remaining?  One way or the other I need to get this database back online.

    Thanks in advance,

    Linn

    Monday, January 16, 2012 4:13 PM

Answers

  • Linn,

    When you attempted to offline the database nicely SQL Server waits for the transactions and connections to finish. Since you already have said that you aren't sure what was causing activity in the database, this is probably the culprit.

    If you would like to stop the database offline command already submitted:

    1. Check sys.dm_exec_requests for the blocked offline command

    SELECT er.Session_id, er.status, er.command
    		, er.blocking_session_id, er.wait_type
    		, er.wait_time, er.wait_resource 
    		, st.text
    FROM sys.dm_exec_requests er CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
    WHERE command = 'ALTER DATABASE'
    

    2. Kill the offline command spid

    KILL Session_ID_Number
    

    When the database is back to normal, you can first set it into single use mode before you restore it ot make sure other connections are out (forcefully). This is disruptive behavoir as you are tellign SQL Server to forcefully kill and rollback any transactions, be careful. Have your restore command ready, I like to alter the database, switch context to master and immediately restore as such:

    ALTER DATABASE My_DB_Name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO
    
    USE master
    GO
    
    RESTORE DATABASE My_DB_Name...
    

    -Sean

     

    Monday, January 16, 2012 5:06 PM
    Answerer
  • Linn,

    To see that you can run:

    select [name], user_access_desc from sys.databases
    

    -Sean

    • Marked as answer by amber zhang Wednesday, January 25, 2012 5:14 AM
    Tuesday, January 17, 2012 2:42 PM
    Answerer

All replies

  • Linn,

    When you attempted to offline the database nicely SQL Server waits for the transactions and connections to finish. Since you already have said that you aren't sure what was causing activity in the database, this is probably the culprit.

    If you would like to stop the database offline command already submitted:

    1. Check sys.dm_exec_requests for the blocked offline command

    SELECT er.Session_id, er.status, er.command
    		, er.blocking_session_id, er.wait_type
    		, er.wait_time, er.wait_resource 
    		, st.text
    FROM sys.dm_exec_requests er CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
    WHERE command = 'ALTER DATABASE'
    

    2. Kill the offline command spid

    KILL Session_ID_Number
    

    When the database is back to normal, you can first set it into single use mode before you restore it ot make sure other connections are out (forcefully). This is disruptive behavoir as you are tellign SQL Server to forcefully kill and rollback any transactions, be careful. Have your restore command ready, I like to alter the database, switch context to master and immediately restore as such:

    ALTER DATABASE My_DB_Name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO
    
    USE master
    GO
    
    RESTORE DATABASE My_DB_Name...
    

    -Sean

     

    Monday, January 16, 2012 5:06 PM
    Answerer
  • Hi Sean,

    I was able to kill the offline process using your suggestions.  I think I know what is still holding onto the database and it is coming from the application server.  The application is using a service called JDataServer to connect to the databases.  I would like to simply restart this service however it is also servicing another application to another database that I can't disrupt right now.

    This is a test database and is in simple mode so I don't have any transactions to worry about.  So I tried the alter database command you suggested and it ran but didn't help.  I still get the 'database is in use' error message when trying to restore the fresh data.  I think I will have to wait until tonight when I can restart that JDataServer service and try it again.

    Thanks,

    Linn

    Monday, January 16, 2012 7:00 PM
  • This is a test database and is in simple mode so I don't have any transactions to worry about.  So I tried the alter database command you suggested and it ran but didn't help.  I still get the 'database is in use' error message when trying to restore the fresh data.  I think I will have to wait until tonight when I can restart that JDataServer service and try it again.


    Linn,

    Simple recovery model still has transactions, no recovery model takes that away.

    As for the database in use, if you put it into single user mode, only 1 user can be in the database. This can be as much as having the database highlighted in the server explorer window in SSMS. You'll have to do a little bit of digging, but if you left the database in single user mode you may want to take it out by running the same command but change SINGLE_USER to MULTI_USER.

    Also, you could easily run sp_who2 and see who is currently in the database (or forcefully take it offline).

    -Sean

    Monday, January 16, 2012 9:34 PM
    Answerer
  • Oh, that was dumb... I had the database selected in the management studio.  I clicked out of that and off it went.

    Here's another dumb question, how do I tell if the database is in single user mode or multi-user mode?

    Thanks,

    Linn

    Tuesday, January 17, 2012 2:40 PM
  • Linn,

    To see that you can run:

    select [name], user_access_desc from sys.databases
    

    -Sean

    • Marked as answer by amber zhang Wednesday, January 25, 2012 5:14 AM
    Tuesday, January 17, 2012 2:42 PM
    Answerer
  • Perfect, thanks Sean I just wanted to confirm everything was correct.

    Thanks!

    Linn

    Tuesday, January 17, 2012 9:50 PM