FIX – The database could not be exclusively locked to perform the operation–TSQL

Sometimes we might get this error when we perform ALTER statements on an existing database. This error means, that particular database is being used by lot of other user connections and the present ALTER statement needs to have an exclusive lock on the database in order to complete the action.

Lets get into an example, I have a large database ‘TestDB’ and it is presently being accessed by other connections. Now let me try to rename it using an ALTER statement.

ALTER DATABASE [TestDB] MODIFY NAME = [TestDBNew];

 

I landed up with the following error –

AlterExclusiveLock

 

To fix this error, we need to take the database into SINGLE USER mode and then ALTER the database, and finally bring the database into MULTI USER mode.

/**************************** Set Single User Mode *****************************/ 
ALTER DATABASE [TestDB]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;


/************************* Alter database to new name **************************/ 

ALTER DATABASE [TestDB] MODIFY NAME = [TestDBNew];


/**************************** Set Multi User Mode *****************************/ 

ALTER DATABASE [TestDBNew]
SET MULTI_USER;

 

Output –

AlterExclusiveLock1

You may also like...