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.
This was first published in April 2002