How do I drop a SQL Server database?

Note that this article describes symptoms and workarounds to any situation where user activity is preventing you from performing some operation on the database, such as detach, backup, restore, shrink, and truncate log. 
 
The easy answer is to issue the following command in Query Analyzer: 
 

DROP DATABASE foo

 
However, you will sometimes receive the following error: 
 

Server: Msg 3702, Level 16, State 3, Line 1  Cannot drop the database 'foo' because it is currently in use.

 
Hopefully you aren't in the habit of trying to drop production databases; so, more often than not, this error occurs because your current Query Analyzer window is actually set to the context of foo. So, I usually recommend getting rid of extraneous Query Analyzer windows, and using the following command instead: 
 

USE MASTER  GO    DROP DATABASE foo

 
You might still get the error, if other users are connected without your knowledge. One way to get rid of them immediately: 
 

ALTER DATABASE foo      SET SINGLE_USER      WITH ROLLBACK IMMEDIATE

 
This will drop kick any connections and roll back their transactions, at which point you should be able to drop the database successfully. 
 
However, this leaves out SQL Server 7.0 users, who don't have access to such a command. Here is a stored procedure I created for 7.0: 
 

CREATE PROCEDURE dbo.clearDBUsers      @dbName SYSNAME  AS  BEGIN      SET NOCOUNT ON        DECLARE @spid INT,          @cnt INT,          @sql VARCHAR(255)        SELECT @spid = MIN(spid), @cnt = COUNT(*)          FROM master..sysprocesses          WHERE dbid = DB_ID(@dbname)          AND spid != @@SPID        PRINT 'Starting to KILL '+RTRIM(@cnt)+' processes.'            WHILE @spid IS NOT NULL      BEGIN          PRINT 'About to KILL '+RTRIM(@spid)            SET @sql = 'KILL '+RTRIM(@spid)          EXEC(@sql)            SELECT @spid = MIN(spid), @cnt = COUNT(*)              FROM master..sysprocesses              WHERE dbid = DB_ID(@dbname)              AND spid != @@SPID            PRINT RTRIM(@cnt)+' processes remain.'      END  END  GO

 
Sample usage: 
 

EXEC dbo.clearDBUsers 'foo'

 
You might have to call the procedure multiple times before it completes the process of wiping out existing users. 
 
And because it happens so often, I'm going to suggest again: please make sure that your current Query Analyzer window isn't the elusive process that won't go away! Make sure you are using the Master database when trying to drop user databases!