ProblemToday 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
|
No comments:
Post a Comment