Detaching, Dropping SQL Server Databases–Some gotchas

There’s a couple of different ways to drop/detach databases from sql server (assuming the obvious Right Click, Delete option in Management Studio did not work for you)

Method 1  – Make it Offline First

One of the surest ways to drop/detach a SQLServer database involves – making it offline first

ALTER DATABASE foo SET OFFLINE WITH ROLLBACK IMMEDIATE

Now, it is ready to be dropped.

DROP DATABASE foo

Method 2 – Use inbuilt stored proc

You can also try the Detach stored proc

sp_db_detach foo

What can go wrong – Database currently in use?

You may encounter an error  ‘Unable to drop….This database is currently in use….’ . To fix this error, you need find out the user sessions currently open:

1. select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
     from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
   where d.name = ‘foo’
   
2. Then, kill that session – kill sessionID

You should be able to try the two methods above once you are done killing all open sessions to the database.

What can go wrong – Database in single user mode?

You may find the database is in Single User Mode –and doesn’t allow you to run any commands against it (not even the command of Right Clicking—>View Properties). The fix for this involves setting the database back to multi-user

1. select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
     from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
   where d.name = ‘foo’
   
2. Then, kill that session – kill sessionID
   
3. Then, exec sp_dboption ‘testdb01’, ‘single user’, ‘FALSE’

NOTE: sp_dboption is gone from Denali onwards. We can use SQL Server Management Studio to accomplish the same thing. Just need to right click the specific database–>Properties–>Options–>restrict access–>Change Singleuser to multiple

What can go wrong – sa user unable to attach database (mdf) files?

See the workaround here