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

Neeks
Posted by in Sql Server category on for Beginner level | Views : 11167 red flag
Rating: 5 out of 5  
 1 vote(s)

You might get the error related to "Exclusive access could not be obtained because the database is in use" so, in this article we will gain over this error.

Introduction

While you are restoring the database from the backup you might get the error

"Exclusive access could not be obtained because the database is in use". This article will help to overcome this error at the time of restore the database.

Why you are getting this error?


This error occurs only when the connection to the database is open and you are trying to restore the database.

Way to resolve the error.


You have to close all active connections.
Way to resolve the error.

You have to select the database to connect to database engine.

Use [Master] --We have used the default database available in all sql server

Go

--Now declare the variable for the database name 

DECLARE @DatabaseName VARCHAR(50)

 

SET @DatabaseName = ‘DBName’ -- Set the database name

--Declare the table to save the SID which are connected to the Database

DECLARE @SIDList TABLE

(

          ID INT IDENTITY(1,1),

          SID INT

)

--Now, select the SPIDs and insert it in the table from where we are going to kill them

--Now insert the records in the Created table

INSERT INTO @SIDList

SELECT spid FROM master.dbo.sysprocesses WHERE dbid = db_id(@dbname)

DECLARE @CurrentRecord INT,

              @TotalRecord INT

--Get the count of total connections

SELECT @TotalRecord = COUNT(*) FROM @ SIDList

--Set the current record as zero

SET @CurrentRecord = 0

DECLARE @SIDVar INT

WHILE(@CurrentRecord < @TotalRecord)

BEGIN

          --Increment the counter

          SET @CurrentRecord = @CurrentRecord + 1

          SELECT @SIDVar = SID FROM @SIDList WHERE ID = @CurrentRecord


--Now, we got the ID to kill the process

EXECUTE(‘KILL’ + CAST(@SIDVar AS VARCHAR(10))) --Kill the connection

END

Conclusion

So, this way we can remove the error of database already in use.

Page copy protected against web site content infringement by Copyscape

About the Author

Neeks
Full Name: nikhil chauhan
Member Level: Bronze
Member Status: Member
Member Since: 11/25/2008 9:12:37 PM
Country:

http://www.neeks4ever.blogspot.com/
Keep posting and Enjoy coding...

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)