How to drop recalcitrant databases in MSSQL

  • There are times when you want to drop databases, especially development data, but it does not work, there are still users logged on or ... 
Server: Msg 3702, Level 16, State 3, Line 1 
Cannot drop the database sql911 because it is currently in use.

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

Drop database

  1. ALTER DATABASE sql911
  2.     SET single_user WITH ROLLBACK IMMEDIATE;
  3. go
  4. ALTER DATABASE sql911
  5.     SET offline;
  6. go
  7. USE master;
  8. go
  9. DROP DATABASE sql911;
Command(s) completed successfully.
  • Notice that you have no confirmation of the dropping of the database.
  • Line 2: set single_user with rollback immediate;: converts the database to a single user. It does it now and rolls back any not committed transaction.
  • Line 4: set offline;: places the database off line so no user can connect to it.
  • Line 6: drop database sql911;: You first must be in the master database, then you can drop it.
  • Dropping a database does NOT delete the data files. It only removes the pointers to the physical files from the master database.
  • You should always to a full backup before dropping a database, just in case... It's called: belt and suspenders
  1. BACKUP DATABASE sql911 TO DISK = 'c:\temp\sql911-20071016.data.backup' WITH init;
  2. go
  3. BACKUP LOG sql911 TO DISK = 'c:\temp\sql911-20071016.log.backup' WITH init;
  4. go
  • Dropping a database does NOT delete the data files. It only removes the pointers to the physical files from the master database. You can put them back with the attach command.