Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Restore Issues - Database In use mode? Expand / Collapse
Posted Saturday, December 14, 2013 5:29 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 1, 2016 9:51 PM
Points: 1,184, Visits: 3,465

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..


Post #1522941
Posted Saturday, December 14, 2013 8:49 AM


Group: General Forum Members
Last Login: Wednesday, November 16, 2016 9:57 AM
Points: 6,147, Visits: 13,680
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


Post #1522952
Posted Saturday, December 14, 2013 3:29 PM



Group: General Forum Members
Last Login: Today @ 9:11 AM
Points: 42,077, Visits: 39,463
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 ROLLBACK IMMEDIATE, 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."

Helpful Links:
How to post code problems
How to post performance problems
Post #1522980
Posted Sunday, December 15, 2013 11:59 PM


Group: General Forum Members
Last Login: Thursday, March 17, 2016 5:13 AM
Points: 2,796, Visits: 2,232
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.


"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Post #1523096
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse