How to automatically kill a deadlock process in SQL Server2000

1. Create the storage process sp_killlock

Create proc sp_killlock

@ Kill_lock_spid bit = 1, -- whether to kill the deadlock process; 1: Kill; 0: only show

@ Show_spid_if_nolock bit = 1 -- if there is no deadlock in the process, whether the normal process information is displayed, 1 is displayed, 0 is not displayed

As

Declare @ count int, @ s nvarchar (1000), @ I int

Select id = identity (INT, 1, 1), flag,

Process id = spid, thread id = kpid, block process id = blocked, Database ID = dbid,

Database Name = db_name (dbid), user id = uid, user name = loginame, accumulated CPU time = CPU,

Login Time = login_time, number of opened transactions = open_tran, Process status = status,

Work station name = hostname, ApplicationProgramName = program_name, wks process id = hostprocess,

Domain name = nt_domain, NIC address = net_address

Into # T from (

Select flag = 'deadlocked process ',

Spid, kpid, A. Blocked, dbid, uid, loginame, CPU, login_time, open_tran,

Status, hostname, program_name, hostprocess, nt_domain, net_address,

S1 = A. spid, S2 = 0

From master .. sysprocesses a join (

Select blocked from Master... sysprocesses group by blocked

) B on A. spid = B. Blocked where a. Blocked = 0

Union all

Select '| _ victim _> ',

Spid, kpid, blocked, dbid, uid, loginame, CPU, login_time, open_tran,

Status, hostname, program_name, hostprocess, nt_domain, net_address,

S1 = blocked, S2 = 1

From master .. sysprocesses A where blocked <> 0

) A order by S1, S2

Select @ COUNT = @ rowcount, @ I = 1

If @ COUNT = 0 and @ show_spid_if_nolock = 1

Begin

Insert # T

Select flag = 'normal process ',

Spid, kpid, blocked, dbid, db_name (dbid), uid, loginame, CPU, login_time,

Open_tran, status, hostname, program_name, hostprocess, nt_domain, net_address

From master .. sysprocesses

Set @ COUNT = @ rowcount

End

If @ count> 0

Begin

Create Table # T1 (ID int identity (255), a nvarchar (30) null, B INT null, eventinfo nvarchar () null)

If @ kill_lock_spid = 1

Begin

Declare @ spid varchar (10), @ sign varchar (10)

While @ I <= @ count

Begin

Select @ spid = process ID, @ sign = sign from # t where id = @ I

Insert # T1 exec ('dbcc inputbuffer ('+ @ spid + ')')

If @ sign = 'deadlocked process' exec ('Kill '+ @ spid)

Set @ I = @ I + 1

End

End

Else

While @ I <= @ count

Begin

Select @ s = 'dbcc inputbuffer ('+ Cast (process ID as varchar) +') 'from # t where id = @ I

Insert # T1 exec (@ s)

Set @ I = @ I + 1

End

Select a. *, the SQL statement of the Process = B. eventinfo

From # t a join # t1 B on A. ID = B. ID

End

Go

Set quoted_identifier off

Go

Set ansi_nulls on

Go

2. Create a batch file klock. bat

C:

Cd c: \ Program Files \ Microsoft SQL Server \ 80 \ tools \ binn

Del D: \ klock.txt

ISQL-USA-ssvr-p-ID: \ klock. SQL> D: \ klock.txt

3. Create an SQL file klock. SQL that calls the Stored Procedure

Sp_who2

Go

Sp_lock

Go

Sp_who_lock

Go

Exec sp_killlock

Go

Exit