Click here to Skip to main content
15,868,035 members
Articles / Programming Languages / SQL

Exclusive Access Could Not Be Obtained Because the Database is in Use ~ Resolved

Rate me:
Please Sign up or sign in to vote.
4.86/5 (12 votes)
19 Oct 2012CPOL 140.3K   17   10
How to resolve this issue

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

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

SQL
alter database database_name
set offline with rollback immediate
go

alter database database_name
set online
go

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 'Method 2'.

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

License

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


Written By
Technical Lead Air Liquide Industrial Services (Singapore)
Singapore Singapore
My passion lies in building business intelligence and data-based solutions, writing about things I work with and talking about it. New technologies relevant to my line of work interest me and I am often seen playing with early releases of such technologies.

My current role involves architecting and building a variety of data solutions, providing database maintenance and administration support, building the organization’s data practice, and training and mentoring peers.

My aspiration over the next several years is to achieve higher competency and recognition in the field of Data Analytics and move into a career of data science.


Specialities: SQL Server, T-SQL Development, SQL Server Administration, SSRS, SSIS, C#, ASP.Net, Crystal Reports

Comments and Discussions

 
GeneralVisual Error - First line of method 2 script- additional line break not interpreted due to Pin
Ali Fakoor17-Oct-15 22:14
Ali Fakoor17-Oct-15 22:14 
GeneralRe: Visual Error - First line of method 2 script- additional line break not interpreted due to Pin
Manjuke Fernando26-Oct-15 16:58
professionalManjuke Fernando26-Oct-15 16:58 
SuggestionTail-log backup Pin
AndySugs15-Jul-15 4:50
AndySugs15-Jul-15 4:50 
GeneralThnaks Pin
mohamedenew18-Jul-13 0:26
mohamedenew18-Jul-13 0:26 
GeneralMy vote of 5 Pin
bonfirebmt5-Jun-13 15:40
bonfirebmt5-Jun-13 15:40 
GeneralMy vote of 5 Pin
ziaur18-Oct-12 22:48
ziaur18-Oct-12 22:48 
QuestionA question Pin
Pablo Aliskevicius16-Jan-12 21:08
Pablo Aliskevicius16-Jan-12 21:08 
AnswerRe: A question Pin
Manjuke Fernando17-Jan-12 5:08
professionalManjuke Fernando17-Jan-12 5:08 
It doesn't accept variables for the kill statement. Unless you give a static value such as :

SQL
kill 55


and throws an error : Incorrect syntax near '@variable'
Manjuke Fernando

GeneralRe: A question Pin
Pablo Aliskevicius17-Jan-12 19:47
Pablo Aliskevicius17-Jan-12 19:47 
GeneralMy vote of 5 Pin
Pablo Aliskevicius16-Jan-12 21:00
Pablo Aliskevicius16-Jan-12 21:00 

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

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