KILL SQL Server 2000 Blocking SPID and Records it details


Problem
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
Advertisements

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s