Click here to Skip to main content
Click here to Skip to main content
Technical Blog

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

, 19 Oct 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
How to resolve this iisue.


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)
    print 'Killing process ' + cast(@spid as varchar) + ' ...'
    set @sql = 'kill ' + cast(@spid as varchar)
    exec (@sql)

        @spid = min(spid)  
        dbid = db_id('<database_name>') 
        and spid != @@spid

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<br>set offline with rollback immediate
alter database database_name
set online

Method 3

Setting the database to single user mode and later, setting to multi user will also close all the active connections. And this method is faster than the 'Method 2'.

use master
alter database <dbname>
set single_user with rollback immediate
alter database <dbname>
set multi_user


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Manjuke Fernando
Technical Lead Air Liquide Industrial Services (Singapore)
Singapore Singapore
I have been in software industry for more than 8 years. I have developed different type of software using different languages. Many of them are database related (both web & window based), SQL being as the back end most of the time. Up-to-date I have knowledge in languages such as C#, VB.Net, T-SQL, JAVA, VB6 & C++, making C# the most proficient of all. Also I have worked using different technologies like ASP.Net, SharePoint, Crystal Reports (But I really hate designing reports) & MS SQL Server and have involved in designing & developing software for major companies like FedEx, Softlogic Holdings, IronOne Technologies & Brandix. Currently I am working as a Tech Lead in Singapore.
Follow on   Twitter   LinkedIn

Comments and Discussions

GeneralThnaks Pinmembermohamedenew18-Jul-13 1:26 
GeneralMy vote of 5 Pinmemberbonfirebmt5-Jun-13 16:40 
GeneralMy vote of 5 Pinmemberziaur18-Oct-12 23:48 
QuestionA question PinmemberPablo Aliskevicius16-Jan-12 22:08 
AnswerRe: A question PinmemberManjuke Fernando17-Jan-12 6:08 
It doesn't accept variables for the kill statement. Unless you give a static value such as :
kill 55
and throws an error : Incorrect syntax near '@variable'
Manjuke Fernando

GeneralRe: A question PinmemberPablo Aliskevicius17-Jan-12 20:47 
GeneralMy vote of 5 PinmemberPablo Aliskevicius16-Jan-12 22:00 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150327.1 | Last Updated 19 Oct 2012
Article Copyright 2012 by Manjuke Fernando
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid