CODEWITHSUNDEEP
sql-server
Allen S
Allen S

Reputation: 53

ALTER DATABASE failed because a lock could not be placed on database <MyDB>

I have a program need to create and drop a database multiple time, but sometime when dropping a database I get a exception here:

ALTER DATABASE failed because a lock could not be placed on database ...

And the command is like this:

USE master; 

IF EXISTS(select * from sys.databases where name='{0}')

BEGIN 

ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE [{0}]

END

Why it happens?

What is the better way to do this (drop database)?

Upvotes: 2

Views: 276

Answers (1)

Thom A
Thom A

Reputation: 95569

You can't DROP a database if any one else is connected to it. Simply running DROP DATABASE MyDatabase; doesn't close those connections, thus the DROP fails.

Changing the database to SINGLE USER will drop any existing connections (WITH ROLLBACK IMMEDIATE causes any transactions to be rolled back immediately, which is a problem here, as your about the DROP the database). Then, because it's instantly the next statement, the database is dropped before anyone gets the opportunity to reconnect.

Upvotes: 2

Related Questions