Kill SPIDs and Get Away With It By Parameter

So every now and again there is a need to kick everyone out of a database in a hurry.  The simple way is to kill the SPID.  Execute sp_who2 and look for the connections that need to die.  Using the Kill command with the SPIDS that need to die is easy enough.  Unless there are a lot of connections and more coming all the time.

This happens to me from time to time with an application where the database needs to go offline and back as quickly as possible.  Except there tend to be a fair number of users and there are services that connect automatically in short intervals of a minute or so.  The database won’t go offline if there are active connections.  The solution is to Kill the connections.  Not a big deal since they aren’t working already.  But there is a timing issue to get all the SPIDs killed qucikly before they reconnect and the database a prevent it from going offline.

This is where this little script comes in handy.  It can be run at will and will return results similar to sp_Who2.  The difference here is that there are parameters that can be set to filter the results.  Additionally the results have the code in the first column, DeathToTheseSPIDs, that can be copied and pasted from the results pane in SSMS into a command window and executed.

The parameters are all optional.  Just fill in the criteria needed to get just the SPIDs that need to die.  Perhaps you just need to kill all the connections from one  host or a particular user but only on a specific database.  Just fill in the values you need to filter and leave the rest as NULL. 

Technically the script can be executed against any database since it only draws from one table in the master db.  The use of a 3 part name in the FROM clause solves that issue.  However, it is always helpful to ensure you don’t wind up killing your own connection.

It is trivial to modify the WHERE criteria or actually execute the KILL statements.  This script doesn’t do it automatically to prevent troubling accidents.

Enjoy, modify to your own needs:

-------------------------------------------------------------------------------
-- Simple script to return a list of SPIDs to kill
-------------------------------------------------------------------------------
-- Purpose: Generate a list of spids to kill an the code to get it done based on
    -- input parameters
    
-- Date: 10/27/2009
-- Version: 1.00.000
-- Author: Joshua Lynn


-- Notes:
    -- replace some or all NULL default vaules for variables to filter the list
    -- Copy & Paste DeathToTheseSPIDs to command window and execute

DECLARE @DB_Name    sysname
DECLARE @dbid        SMALLINT
DECLARE @LoginName    sysname
DECLARE @HostName    sysname

SET @DB_Name    = NULL 
SET @dbid        = NULL 
SET @LoginName    = NULL
SET @HostName    = NULL 


SELECT
      'KILL ' + CAST(sp.[spid] AS VARCHAR(10)) AS DeathToTheseSPIDs
    , sp.[spid]
    , sp.[status]
    , DB_NAME(sp.[dbid]) AS dbName
    , sp.[hostname]
    , sp.[program_name]
    , sp.[cmd]
    , sp.[cpu]
    , sp.[physical_io]
    , sp.[blocked]
    , sp.[dbid]
    , CONVERT(SYSNAME, RTRIM(sp.[loginame])) AS loginname
    , sp.[spid] AS 'spid_sort'
    , SUBSTRING(CONVERT(VARCHAR, sp.[last_batch], 111), 6, 5) + ' '
    + SUBSTRING(CONVERT(VARCHAR, sp.[last_batch], 113), 13, 8) AS 'last_batch_char'
    , sp.[request_id]
FROM
    master.dbo.sysprocesses sp WITH ( NOLOCK )
WHERE
    DB_NAME(sp.[dbid]) = ISNULL(@DB_Name, DB_NAME(sp.[dbid]))
        AND 
    sp.[dbid] = ISNULL(@dbid, sp.[dbid])
        AND    
    CONVERT(SYSNAME, RTRIM(sp.[loginame])) = ISNULL(@LoginName, CONVERT(SYSNAME, RTRIM(sp.[loginame])))
        AND
    sp.[hostname] = ISNULL(@HostName, sp.[hostname])

    

Comments

[sqlity]

Hi Joshua,
The easiest way to get all users out of a database:
ALTER DATABASE <DbName> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

That kills all connections to that database. If you issue your DROP DATABASE or RESTORE DATABASE right away after it, you should be fine even on a busy system. Only replication sometimes can be faster back in than you. In that case us OFFLINE instead of SINGLE_USER. A restore after this will work fine, but if you drop the database, you have to manually delete the files.

As for killing your own spid, SQL Server wont let you. You will get an error that will politely tell you, that suicide is not permitted in SQL.

S

[Joshua Lynn]

Typing ALTER DATABASE is a bit nerve racking in a production environment.

The goal isn't always to set single user mode. Sometimes it just an expedient way to get everyone out. Mostly in a development environment

The scenario where I needed this is on an application that every few weeks stops responding normally. The particulars escape me know but there was a reason this method needed to be done. Commands didn't seem to be working but killing the spids freed it up so the database could be taken offline and the put right back online. This clears up the problem and only takes a minute or two. At some point contacting the vendor, with whom we have a contract, seems like a wise thing to do but the workaround is just too easy.

Next time it happens I will try your suggestion with the script to take the db offline and then back on and report how it went.