SQL Server: Kill Process using Stored Procedure

Question

I want to modify the following as it doesn't seem to kill processes - I think its supposed to disconnect users (is this the same?). I want to be able to kill all process for a particular database - how can I modify the below:

create procedure [dbo].[sp_killusers](@database varchar(30))
as
----------------------------------------------------
-- * Created By David Wiseman, Updated 19/11/2006
-- * http://www.wisesoft.co.uk
-- * This procedure takes the name of a database as input
-- * and uses the kill statment to disconnect them from
-- * the database.
-- * PLEASE USE WITH CAUTION!!
-- * Usage:
-- * exec sp_killusers 'databasename'
----------------------------------------------------
set nocount on
declare @spid int
declare @killstatement nvarchar(10)

-- Declare a cursor to select the users connected to the specified database
declare c1 cursor for select request_session_id
                                from sys.dm_tran_locks
                                    where resource_type='DATABASE'
                                    AND DB_NAME(resource_database_id) = @database
open c1
fetch next from c1 into @spid
-- for each spid...
while @@FETCH_STATUS = 0
begin
      -- Don't kill the connection of the user executing this statement
      IF @@SPID <> @spid
      begin
            -- Construct dynamic sql to kill spid
            set @killstatement = 'KILL ' + cast(@spid as varchar(3))
            exec sp_executesql @killstatement
            -- Print killed spid
            print @spid
      end
      fetch next from c1 into @spid
end
-- Clean up
close c1
deallocate c1

Update

The above doesn't work i.e. it doesn't kill the process.

It doesn't kill the process. I look at the activity monitor and its still shows the process continuing and I can see my query still working in the query window. When I do "kill 53", the querying stops in the query window and the process is gone from the activity monitor! So th kill works but not this procedure why?

Answer 1

Odds are good that none of these apply to you, but just in case here are some oddball situations I encountered when working on stuff like this a few years back (all SQL 2005).

• You can't kill your own connection.
• In the code I used, I made sure to never try and kill any spid under 51. (These are system connections; I don't know if they can be killed, but I wouldn't try it.)
• If a connection is processing a transaction, it has to roll that transaction back before it can be killed. Huge transactions can take significant time to roll back.
• Beware connection pooling. They're like the undead--kill them, and they just come right back, often in under a second.

Running SQL Profiler and tracking logins and logouts while you run this process might be revealing, particularly for connection pooling issues.

Answer 2

You might want to try using exec instead of sp_exec (not that it should make any difference)

SET @killstatement = 'KILL ' + cast(@spid as varchar(3)) 
EXEC (@killstatement)

Answer 3

This works for me in SQLServer 2000

DECLARE @DbName VARCHAR(100)
DECLARE @SPID INT
DECLARE @TranUOW UNIQUEIDENTIFIER
DECLARE @KillStmt NVARCHAR(100)

SET @DbName = 'MyDatabase'

-----------------------------------
-- Kill distributed transactions

DECLARE dist CURSOR FOR
    SELECT DISTINCT req_transactionUOW
        FROM master..syslockinfo
        WHERE db_name(rsc_dbid) = @DbName
              AND req_transactionUOW <> '00000000-0000-0000-0000-000000000000'

OPEN dist

FETCH NEXT FROM dist INTO @TranUOW

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @KillStmt = 'kill ''' + CAST(@TranUOW AS VARCHAR(50)) + ''''

    PRINT @KillStmt
    EXECUTE(@KillStmt)

    FETCH NEXT FROM dist INTO @TranUOW
END

CLOSE dist
DEALLOCATE dist

-----------------------------------
-- Kill user connections

DECLARE cur CURSOR FOR
    SELECT spid
        FROM master..sysprocesses
        WHERE db_name(dbid) = @DbName
              AND spid > 50

OPEN cur

FETCH NEXT FROM cur INTO @SPID

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @KillStmt = 'kill ' + CAST(@SPID AS VARCHAR(10))

    PRINT @KillStmt
    EXECUTE(@KillStmt)

    FETCH NEXT FROM cur INTO @SPID
END

CLOSE cur
DEALLOCATE cur

Answer 4

Are you just trying to stop all activity on a particular DB so you can do some maintenance on it?

If so, you can do the following:

ALTER DATABASE myDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

This will kill all other SPIDs accessing the DB, and will put the DB in single-user mode. Then perform your maintenance action, and do the following afterwards:

ALTER DATABASE myDB SET MULTI_USER;

Answer 5

I'm familiar with this script. It kills all SPIDs that are using a database, yes. You need to run it under the correct permissions - not just any user can kill SPIDs.

Also, there's a chance you may have applications that try and maintain persistent connections to the DB, and therefore may reconnect shortly after you kill their SPID.

Answer 6

Have you tried any debugging/output of what actually occurs when the procedure is run? For example, can you modify you @killstatement to be declared as nvarchar(max) and include some verbose output such as the following and post the results? Basically replace everything within your begin/end block with something like:

-- Construct dynamic sql to kill spid
select  @killstatement = N'
            select  *
            from    sys.dm_exec_sessions s
            join    sys.dm_exec_connections c
            on      s.session_id = c.session_id
            where   c.session_id = @spid;

            kill ' + cast(@spid as varchar(3)) + ';

            select  *
            from    sys.dm_exec_sessions s
            join    sys.dm_exec_connections c
            on      s.session_id = c.session_id
            where   c.session_id = @spid;           
        ';
-- Print & Exec
print @killstatement;
exec sp_executesql @killstatement, N'@spid smallint', @spid;
print @spid;

There's no reason anything should be behaving differently within the procedure code vs. executing explicitly within a connection - assuming you have the appropriate permissions, are killing valid spids, etc., etc. If you can post the results of some debugging like the above (and anything else you may have tried), it would help figure out where the issue is. You might also want to include a debug output of the results of the cursor declare you are using to make sure you are actually getting the sessions you are trying to kill - i.e. simply include the same select you are using in your cursor declare to output a result set, like this:

declare c1 cursor for select request_session_id
                                from sys.dm_tran_locks
                                    where resource_type='DATABASE'
                                    AND DB_NAME(resource_database_id) = @database

-- Debug output - sessions we should try and kill...
select  request_session_id
from    sys.dm_tran_locks
where   resource_type='DATABASE'
AND     DB_NAME(resource_database_id) = @database;

If you can post the results, hopefully that will give us something to go on.