/* -- Handle deadlocks
View the current process or deadlock process and automatically kill the dead process
Because it is intended for dead, 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.
-- Producer build 2004.4 --*/
/* -- 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
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,
Workstation name = hostname, application name = program_name, workstation 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), B Int, EventInfo nvarchar ))
If @ kill_lock_spid = 1
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.