Kill the blocking connections

Many client-server applications do not close their database connections when terminated abnormally or should a user power off their client. This can result in un-committted database updates blocking other database connection updates.

To simulate the block condition, open two sessions in Query Analyzer and run the SQL below in each window. The first window executed should block the other window.

use Northwind 
go 
begin transaction 
select @@spid 
update categories 
set CategoryName = 'change ' + cast (@@spid as varchar(6) ) 
where CategoryID = 1 

This stored procedure (sp_KillBlockers) will kill the blockers that have not issued a database command in the specified number of seconds and record process information in table master.dbo.DT3_BlockersKilled.

Since this stored procedures utilizes the system function "kill" and SQL Server only allows a member of the "system administrator" or "process administrator" roles to perform a kill, you will need an appropriate database login such as "sa".

This procedure has two parameters: @Seconds and @Debug. @Seconds is the number of seconds that the blocking database connection has been idle. @Seconds has a default value of 60 seconds. @Debug is a indicator for simulating the use of the kill. When set to 'Y', the kills are not actually performed but are selected. @Debug defaults to false ('N').

When blocking database connections are killed, an audit trail is saved in table master.dbo.DT3_BlockersKilled. Note that one cannot kill a system process and if one tries, error message 6107 "Only user processes can be killed." will occur.

create table DT3_BlockersKilled 
( KilledTs      datetime        not null default getdate() 
, LoginName     nchar (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
, DatabaseName  sysname         NOT NULL 
, LoginTs       datetime        NOT NULL 
, LastCommandTS datetime        NOT NULL 
, HostName      nchar (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
, ProgramName   nchar (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
, LastCommandText nchar (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
, NtDomainName  nchar (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
, NtUserName    nchar (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
, NetAddress    nchar (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
, NetLibrary nchar (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) 
GO 

create  procedure sp_killBlockers 
( @Seconds int = 60 
, @Debug        char(1) = 'N' 
) 
as 
/* 

To cause a block, open two sessions in Query Analyzer and run the below SQL in each window. The first window executed should block the other window:

use Northwind 
go 
begin transaction 
select @@spid 
update categories 
set CategoryName = 'change ' + cast (@@spid as varchar(6) ) 
where CategoryID = 1 

*/ 
set nocount on 
declare @killSpid       smallint 
,       @Error          int 
,       @RowCount       int 
,       @killSQl        varchar(128) 

SELECT  spid 
,       loginame        as LoginName 
,       db_name(dbid)   as DatabaseName 
,       login_time      as LoginTs 
,       last_batch      as LastCommandTS 
,       hostname        as HostName 
,       program_name    as ProgramName 
,       cmd             as LastCommandText 
,       nt_domain       as NtDomainName 
,       nt_username     as NtUserName 
,       net_address     as NetAddress 
,       net_library     as NetLibrary 
into #Killers 
from  master.dbo.sysprocesses sysprocesses 
where spid > 12 
and last_batch <= dateadd(ss, -1 * @Seconds, getdate() ) 
and blocked = 0 
AND EXISTS 
(select 1 from sysprocesses as Blockers 
where Blockers.blocked = sysprocesses.spid 
) 
select  @error = @@error , @rowcount = @@rowcount 
If      @error <> 0 return @error 
IF @Debug = 'Y' select * from #Killers 

set @killSpid    = 0 
set rowcount 1 
while 1 = 1 
begin 
        select  @killSpid = spid from #Killers where spid > @killSpid   
        select  @error = @@error , @rowcount = @@rowcount 
        If      @error <> 0 return 
        if      @rowcount = 0 BREAK 
        IF      @Debug = 'Y' 
        BEGIN 
                select * 
                from    #Killers 
                where   spid = @killSpid 
        END 
        ELSE 
        BEGIN 
                set     @KillSQL = 'Kill ' + cast(@killSpid as varchar(8) ) 
                exec ( @killSQl) 
                INSERT INTO master.dbo.DT3_BlockersKilled 
                        ( LoginName, DatabaseName, LoginTs 
                        , LastCommandTS, HostName, ProgramName 
                        , LastCommandText, NtDomainName, NtUserName 
                        , NetAddress, NetLibrary) 
                select    LoginName, DatabaseName, LoginTs 
                        , LastCommandTS, HostName, ProgramName 
                        , LastCommandText, NtDomainName, NtUserName 
                        , NetAddress, NetLibrary 
                from    #Killers 
                where   spid = @killSpid 
        END 
END 

GO 

For More Information

• What do you think about this tip? E-mail the Editor at tdichiara@techtarget.com with your feedback.
• The Best SQL Server Web Links: tips, tutorials, scripts, and more.
• Have an SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
• Check out our Ask the Experts feature: Our SQL, Database Design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.