Post reply

Restore Issues - Database In use mode?

  • December 14, 2013 at 5:29 am

    #395989

    HI.

    Generally MS SQL SERVER 2008 R2 Entrprise Editon allowed for online restore process.. my case i am using same edition why it is not allowed online restore operation?

    Database always says database in use and Exclusive access could not be obtained because the database is in use

    Please confirm, any SPID blocked that process?

    I have tried, set single user mode not help.. and kill SPID that praticular database but not help..

    Thanks

    ananda

  • December 14, 2013 at 8:49 am

    #1673471

    presuming this is a user database you are trying to restore?

    run select * from master..sysprocesses where db_name(dbid) = 'your database'

    kill any spids this returns and run our restore (in the same batch)

    If the connections keep getting to the database before you can run

    alter database your database set offline with rollback immediate;

    then your restore in the same batch

    ---------------------------------------------------------------------

  • December 14, 2013 at 3:29 pm

    #1673488

    First of all, I strongly recommend against killing SPIDs for anything ever. It's a documented problem on CONNECT that the KILL process can leave spids that consume large amounts of resources trying to do a "zero rollback" that will never finish because there's actually nothing to rollback. To date, the only fix for such "zero rollback" spids is to bounce the SQL Server Service. I have witnessed/been victim to this problem myself.

    Instead of killing spids, write a script to set the database to single user mode with [font="Arial Black"]ROLLBACK IMMEDIATE[/font], immediately change it back to multi-user mode (in case you lose the connection, you don't want something else grabbing the only connection available), and immediately start the restore all within the same script. The attached Webservices and the like won't be fast enough to do a reconnect before the restore starts if you do those 3 steps in a single T-SQL script (I do this all the time on my Dev and Staging boxes when doing restores/refreshes). Don't forget, your SSMS session must not be attached to the database while running the script.

    I also don't take the database offline unless there are other compelling reasons to do so. I let the restore do that.

    Second, the question is about ONLINE restores. That would imply NOT killing any connections. I believe the real answer here is that you can't do an "online" restore if the only filegroup available is the PRIMARY filegroup. In fact, according to what's in Books Online ("restores, online" in 2005 and "Piecemeal" restores in 2005 and up), I don't believe you can do an "online" restore of the PRIMARY FG even if multiple other FGs exist because the PRIMARY FG must be up and fully functional at all times for the database to be "online" to begin with.

    All that being said, I strongly recommend you spend some time on "restores" in Books Online before you try anything else. To do otherwise is like a blind man smoking a cigarette in a gunpowder factory. 😉 And, just to be sure, "Books Online" is the help system you can get to by pressing the {f1} key while in SSMS.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 15, 2013 at 11:59 pm

    #1673576

    Hello Anand,

    Looks like you would like to do Online restore and you are doing normal database restore. Go through the following link which will help you to understand the online restore.

    http://technet.microsoft.com/en-us/library/ms188671.aspx

    HTH

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply