SQL Error : Exclusive access could not be obtained because the database is in use
One day I got this error, My Logshipping was stopped due to some system fault, after doing so lots of excersice and googling, at last I decided to take full backup and restore it to Secondary box, but by accident , in between R&D, I stopped Logshipping Restoration Job, due to this database was not accessible/Suspect mode and process was in KILLED/ROLLBACK state in SP_WHO2 output.
The reason was very simple, because DB was being used by Killed/Rollback process thus why DB was in use.
There are so many options to kill this rollback operation like killing associated KPID at OS level, or restart the server.(Here i am not afraid of any data loss, because i was going to restore it on this box, My backup was ready.)
Other way is to take this db in Single user mode using Syntax like :
USE
MASTER
ALTER DATABASE yourdatabasename SET SINGLE_USER WITH ROLLBACK
Then again it in Multiuser, and start restore process
USE
MASTER
ALTER DATABASE yourdatabasename SET MULTI_USER WITH ROLLBACK
Other option is like
-
Find all the active connections, kill them all and restore the database.
-
Get database to offline because this will close all the opened connections to this database, bring it back to online and restore the database.
WikiDBA 