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

Ganapathi Varma | October 26th, 2015 | SQL Backup & Restore

In this post I will show a procedure to resolve the database restore issue.

Below is the error that we often encounter when we restore the database since the database is already used by others.

Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

 

restore error in sql server

We will show you a procedure to resolve this issue.

Step 1:

 

Drop all the connections accessing the database. This can be done either killing the connections or by putting databases in single user mode.

 

Execute SP_WHO2 to find all the connections to the database or we can also use activity monitor dashboard to find those connections.

 

Step 2:

Kill all the connections accessing database using below script.

Kill SPID   — SPID is the session ID of the user process

 

Step 3:

Setting the database to single user mode

 

Use master

GO

ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

RESTORE DATABASE <dbname> FROM DISK = ‘’

GO

ALTER DATABASE <dbname> set multi_user

GO

 

 

Regards
Ganapathi varma

Senior SQL Engineer, MCP

Email: gana20m@gmail.com

 

 

Transaction Log Restore