The usp_KillUsers stored procedure will disconnect all users in any given database. The script works in any release of SQL Server and cycles through the list of users while issuing a KILL command for each one. This is handy when you want to restore a database and can't disconnect the users quick enough. You can also use it in 7.0 before placing the database in single user mode. To execute the script, simply pass it the database name as shown below:
usp_KillUsers 'Northwind'
Make sure you compile the stored procedure in the Master database and make sure you're out of the database yourself before issuing the command. Otherwise, you'll receive this non-fatal error:
Server: Msg 6104, Level 16, State 1, Line 1 Cannot use KILL to kill your own process.
Without further buildup, here's the script for SQL Server 2000 (keep reading if you're a 7.0 user):
CREATE PROCEDURE usp_KillUsers @dbname varchar(50) as SET NOCOUNT ON DECLARE @strSQL varchar(255) PRINT 'Killing Users' PRINT '-----------------' CREATE table #tmpUsers( spid int, eid int, status varchar(30), loginname varchar(50), hostname varchar(50), blk int, dbname varchar(50), cmd varchar(30)) INSERT INTO #tmpUsers EXEC SP_WHO DECLARE LoginCursor CURSOR READ_ONLY FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = @dbname DECLARE @spid varchar(10) DECLARE @dbname2 varchar(40) OPEN LoginCursor FETCH NEXT FROM LoginCursor INTO @spid, @dbname2 WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN PRINT 'Killing ' + @spid SET @strSQL = 'KILL ' + @spid EXEC (@strSQL) END FETCH NEXT FROM LoginCursor INTO @spid, @dbname2 END CLOSE LoginCursor DEALLOCATE LoginCursor DROP table #tmpUsers PRINT 'Done' go
With SQL Server 7.0, there is a minor adjustment. This is because the eid is not a valid column for SP_WHO in SQL Server 7.0. If you have SQL Server 7.0, run the following script:
CREATE PROCEDURE usp_KillUsers @dbname varchar(50) as SET NOCOUNT ON DECLARE @strSQL varchar(255) PRINT 'Killing Users' PRINT '-----------------' CREATE table #tmpUsers( spid int, status varchar(30), loginname varchar(50), hostname varchar(50), blk int, dbname varchar(50), cmd varchar(30)) INSERT INTO #tmpUsers EXEC SP_WHO DECLARE LoginCursor CURSOR READ_ONLY FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = @dbname DECLARE @spid varchar(10) DECLARE @dbname2 varchar(40) OPEN LoginCursor FETCH NEXT FROM LoginCursor INTO @spid, @dbname2 WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN PRINT 'Killing ' + @spid SET @strSQL = 'KILL ' + @spid EXEC (@strSQL) END FETCH NEXT FROM LoginCursor INTO @spid, @dbname2 END CLOSE LoginCursor DEALLOCATE LoginCursor DROP table #tmpUsers PRINT 'Done' go
Feedback
This article has received quiet a bit of emails over the past few days. One I particularly liked was from Darwin Hatheway, who had an alternative procedure. The procedure I provided above was one to get you started and may not be suitable for some environments. Darwin's alternative procedure was a fantastic one through and deserves posting. Here's his email:
I avoid temp tables when I can. If someone has a catalog-level lock in TEMPDB, your temp table create blocks you until the TEMPDB locks clear. This is the procedure I use:
CREATE PROCEDURE kill_database_users @arg_dbname sysname with recompile AS -- kills all the users in a particular database -- dlhatheway/3M, 11-Jun-2000 declare @a_spid smallint declare @msg varchar(255) declare @a_dbid int select @a_dbid = sdb.dbid from master..sysdatabases sdb where sdb.name = @arg_dbname declare db_users insensitive cursor for select sp.spid from master..sysprocesses sp where sp.dbid = @a_dbid open db_users fetch next from db_users into @a_spid while @@fetch_status = 0 begin select @msg = 'kill '+convert(char(5),@a_spid) print @msg execute (@msg) fetch next from db_users into @a_spid end close db_users deallocate db_users GO