/*--Handle Deadlocks
View the current process, or deadlock process, and automatically kill the dead process
Because it is for dead, you can only view the deadlock process if there is a deadlock process
Of course, you can control through the parameters, regardless of whether there is no deadlock, only check the look dead lock process
--Jiangjian 2004.4--*/
/*--Invocation Example
EXEC P_lockinfo
--*/
Create proc P_lockinfo
@kill_lock_spid Bit=1,--whether to kill the deadlock process, 1 to kill, 0 to show only
@show_spid_if_nolock Bit=1-If there is no deadlock process, the normal process information is displayed, 1 is displayed, 0 is not displayed
As
DECLARE @count int,@s nvarchar (+), @i int
Select Id=identity (int,1,1), logo,
Process Id=spid, thread id=kpid, block process id=blocked, database id=dbid,
Database name =db_name (dbid), user id=uid, user name =loginame, Cumulative CPU time =CPU,
Login time =login_time, open transaction number =open_tran, process status =status,
Workstation name =hostname, application name =program_name, workstation process id=hostprocess,
Domain =nt_domain, NIC address =net_address
Into #t from (
Select flag = ' Process of deadlock ',
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 (int,eventinfo), a nvarchar (255), b nvarchar
If @kill_lock_spid =1
http://www.bkjia.com/PHPjc/631035.html www.bkjia.com true http://www.bkjia.com/PHPjc/631035.html techarticle /*--handles deadlocks to view the current process, or deadlock process, and can automatically kill the dead process because it is targeted for death, so if there is a deadlock process, you can only view the deadlock process of course, you could pass parameters ...
