Use master -- must be created in the master database
Go
If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [p_lockinfo] ') and objectproperty (ID, n' isprocedure') = 1)
Drop procedure [DBO]. [p_lockinfo]
Go
/** // * -- Handle deadlocks
View the current process or deadlock process and automatically kill the dead Process
Because it is for deadlocks, if there is a deadlock process, you can only view the deadlock Process
Of course, you can use Parameter Control to check only the deadlock process, whether there is a deadlock or not.
Thanks for the reference information provided by caiyunxia and jiangopen.
-- Producer build 2004.04 (reference please keep this information )--*/
/** // * -- Call example
Exec p_lockinfo
--*/
Create proc p_lockinfo
@ 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
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, accumulated CPU time = CPU,
Login Time = login_time, number of opened transactions = open_tran, Process status = status,
Work station name = hostname, Application Program Name = 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 (1000), B INT, eventinfo nvarchar ))
If @ kill_lock_spid = 1
Begin
Declare @ spid varchar (255), @ sign varchar (255)
While @ I <= @ count
Begin
Select @ spid = process ID, @ sign = sign from # t where id = @ I
Insert # T1 exec ('dbcc inputbuffer ('+ @ spid + ')')
If @ rowcount = 0 insert # T1 (a) values (null)
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)
If @ rowcount = 0 insert # T1 (a) values (null)
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
Order by process ID
End
Set nocount off
Go