2

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)?

Allen S
  • 53
  • 1
  • 7

1 Answers1

2

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.

Thom A
  • 88,727
  • 11
  • 45
  • 75
  • Sounds reasonable, but why the SQL command sometimes works? And it is a recommended way to drop a database from others' suggestion, I just copy paste that. – Allen S Aug 08 '18 at 05:45
  • 1
    @AllenS Why does what sometimes work? Just `DROP DATABASE Mydatabase;`? Probably because no one is connected to the database. – Thom A Aug 08 '18 at 06:57
  • I updated my question, and I mean this exception does not occurs every time drop the database in the program. – Allen S Aug 09 '18 at 01:43