SQL Server View and kill the deadlock Process Code

View: exec p_lockinfo 0, 1
Killing: exec p_lockinfo 1, 1

 

Code
Set Ansi_nulls on
Set Quoted_identifier on
Go


/**/ /*-- Call example

Exec p_lockinfo 0, 1
--*/
Create proc [DBO]. [p_lockinfo]
@ Kill_lock_spid bit = 1 , -- Whether to kill the deadlock process, 1 Kill, 0 Show only
@ Show_spid_if_nolock bit = 1   -- If no deadlocked process exists, check whether the normal process information is displayed, 1 Show, 0 Not Displayed
As
Set Nocount on
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, accumulative CPU time = CPU,
Logon Time = Login_time, number of opened transactions = Open_tran, Process status = Status,
Site name = Hostname, application name = Program_name, wks process ID = Hostprocess,
Domain Name = Nt_domain, NIC address = Net_address
Into # T from (
Select flag = ' Deadlock 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 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 ( 1 , 1 ), A nvarchar ( 30 ), B INT, eventinfo nvarchar ( 255 ))
If @ Kill_lock_spid = 1
Begin
Declare @ spid varchar ( 10 ), @ Sign varchar ( 10 )
While @ I <= @ Count
Begin
Select @ spid = Process ID, @ flag = Flag from # T Where ID = @ I
Insert # T1 exec ( ' DBCC inputbuffer ( ' + @ Spid + ' ) ' )
If @ Rowcount = 0 Insert # T1 (a) values ( Null )
If @ Flag = ' Deadlock 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)
If @ Rowcount = 0 Insert # T1 (a) values ( Null )
Set @ I = @ I + 1
End
Select. * , Process SQL statement = B. eventinfo
From # t a join # t1 B on A. ID = B. ID
Order by process ID
End
Set Nocount off