Monday, 16 January 2012

Exclusive access could not be obtained because the database is in use ~ Resolved

 

img_screen_001

Sometimes this is a common error message that we encounter, when we try to restore a SQL database, which is being used by other users.

This can occur due to various reasons. But the most common incident is, users not closing the Management Studio’s query window after they have finished the query task.

There are few ways of resolving this and restore the database.

1.    Find all the active connections, kill them all and restore the database
2.    Get database to offline (And this will close all the opened connections to this database), bring it back to online and restore the database

Method 1

Use the following script to find and kill all the opened connections to the database before restoring database.

declare @sql as varchar(20), @spid as int

select @spid = min(spid) from master..sysprocesses where dbid = db_id('<database_name>')
and spid != @@spid

while (@spid is not null)
begin
print 'Killing process ' + cast(@spid as varchar) + ' ...'
set @sql = 'kill ' + cast(@spid as varchar)
exec (@sql)

select
@spid = min(spid)
from
master..sysprocesses
where
dbid = db_id('<database_name>')
and spid != @@spid
end

print 'Process completed...'



Method 2


Use the following code to take database offline and bring back to online so that all the active connections will be closed. And afterwards restore the database.



alter database database_name
set offline with rollback immediate
alter database database_name
set online
go

7 comments:

  1. I guess one more option could be to set the Database to Single User mode and then bring it back into Multi User mode.

    ReplyDelete
  2. I have been receiving this error on one of my databases every time I try to restore it. The database is in Multi-user mode only but I still receive the error when restoring the database. Can you let me know what might be the problem for this.

    Regards,
    Sai Reddy

    ReplyDelete
  3. method 1 worked wonders, thanks alot!

    ReplyDelete
  4. If all of the above doesn't work.. clear the checkbox "Take tail-log backup before restore" under the Options tab. NOTE: this is on SQL Management Studio 2012. That worked for me. Regards!

    ReplyDelete
    Replies
    1. ** Thanks for the Tip **

      Try making the database to Single User Mode and bring back to Multi User again:

      USE MASTER
      GO


      ALTER DATABASE Database_Name
      SET SINGLE_USER WITH ROLLBACK IMMEDIATE
      GO

      ALTER DATABASE Database_Name
      SET MULTI_USER
      GO

      Delete
    2. Thanks. I've been looking for quite some time before I found your solution which worked.

      Delete