Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Quickly Disconnect Users

By Brian Knight,

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
Total article views: 16786 | Views in the last 30 days: 21
 
Related Articles
FORUM

"fetch"

"fetch"

FORUM

Cursor - passing @dbname to INSERT STATEMENT

Concatentaion of @dbname from cursor + table name to use in FROM clause

FORUM

USE @DBNAME

Trying to use a variab;e with the USE statement

FORUM

query(dbname,size,recoverymodel)

query(dbname,size,recoverymodel)

FORUM

query(dbname,size,recoverymodel)

query(dbname,size,recoverymodel)

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones