Today I got an email from one of my blog reader; they have an application developed with VB6.0 and SQL Server 2000. Application was developed long back and now their database size is increased as well. Due un-efficient coding they are getting blocking issue and stuck all their application transactions. He asked for writing a query which will execute by SQL Server Agent at every 1 minute and will KILL the culprit SPID. He also wants me to store the KILL transaction history as well.
As a solution I have written below query for him and which working fine now.
-- Create below table in master database create table blkHistory ( SPID int, blocked int, killedSPID int, date datetime default getdate(), querytext varchar(8000) ) -- add below code in to job command text box declare @SPID as int, @blocked as int, @KilledSPID as int declare @querytext as varchar(8000), @sql nvarchar(400) select @SPID = spid,@blocked = blocked from sysprocesses where blocked <> 0 --select spid,blocked from sysprocesses where blocked <> 0 select @sql = 'KILL ' + cast (@blocked as nvarchar(100)) DECLARE @Handle binary(20) SELECT @Handle = sql_handle FROM sysprocesses WHERE spid = @blocked SELECT @querytext = text FROM ::fn_get_sql(@Handle) EXECUTE sp_executesql @SQL If @SPID > 0 begin insert into blkHistory(SPID,blocked,KilledSPID,querytext) values (@SPID,@blocked,@blocked,@querytext) end --you can use below query to retrieve datafrom blocking history select * from master..blkHistory