Saturday, July 21, 2012

Error: Database could not be exclusively locked to perform the operation

You might receive "Database could not be exclusively locked to perform the operation" or many error like such when you are trying to perform operation on database which is already been used by process or some other users.

In order to obtain "Exclusive lock" to perform some critical database operation when someone is already using database you can perform following steps as described below to solve your problem.

Earlier I have blogged in context of restore error, but it is not limited to only restore and since this is very common error you get stuck when you are trying to do some critical database operation i have decided to explain step by step so that it can help me and many others like me who are in same situation. :)


Solution to obtain exclusive locked to perform database operations.


Step 1: Disconnect Connection.
To do so:   File > Disconnect Object Explorer

Step 2: Connect Connection
To do so:  File > Connect Object Explorer

Step 3: Open "New Query" window and run following command
use master
Note: Above command will make your current database to master which is important before we run following sequence of command.

Step 4: Copy and paste following sequence of command in Query window.  Replace the word "MyDatabaseName" with Database name you are trying to get exclusive access.

ALTER DATABASE MyDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

ALTER DATABASE MyDatabaseName SET SINGLE_USER WITH ROLLBACK AFTER 30 

ALTER DATABASE MyDatabaseName SET SINGLE_USER WITH NO_WAIT

ALTER DATABASE MyDatabaseName SET MULTI_USER WITH ROLLBACK IMMEDIATE; 


You are now done and you can now try the command or operation you were trying to perform earlier which was giving you "Database could not be exclusively locked to perform the operation" error. 

1 comment:

Master P said...

Would changing the state of the system databases cause a denial of service on a production system?

I have the problem listed and I'd like to get it corrected without causing a DoS or filing an internal change request, if possible.

Here is a link with lots of into to the issue I'm having http://dba.stackexchange.com/questions/76029/checkdb-fails-on-msdb-and-master-system-databases

Most Recent Post

Subscribe Blog via Email

Enter your email address:



Disclaimers:We have tried hard to provide accurate information, as a user, you agree that you bear sole responsibility for your own decisions to use any programs, documents, source code, tips, articles or any other information provided on this Blog.
Page copy protected against web site content infringement by Copyscape