Find a way for SQL Server query deadlock source SQL Server deadlock monitoring _mssql

Find out the source of deadlock and blocking for SQL Server--Find out the source of SQL Server deadlock and blocking

Copy Code code as follows:

Use master
Go
declare @spid int, @bl int
DECLARE S_cur CURSOR for
Select 0, blocked
From (SELECT * from sysprocesses where blocked>0) a
Where NOT EXISTS (SELECT * from sysprocesses where blocked>0) b
where A.blocked=spid)
Union select spid,blocked from sysprocesses where blocked>0
OPEN S_cur
FETCH NEXT from S_cur into @spid, @bl
While @ @FETCH_STATUS = 0
Begin
If @spid =0
Select ' Causes the database to deadlock is:
' + CAST (@bl as VARCHAR (10)) + ' process number, whose execution SQL syntax is as follows '
Else
Select ' Process # spid: ' + CAST (@spid as VARCHAR (10)) + ' is ' + '
The process number spid: ' + CAST (@bl as VARCHAR (10)) + ' is blocked, and its current process executes the SQL syntax as follows '
DBCC InputBuffer (@bl)
FETCH NEXT from S_cur into @spid, @bl
End
Close S_cur
Deallocate s_cur

View the current process, or the deadlock process, and automatically kill the process--view the current process, or the deadlock process, and automatically kill the process

Because it is dead, so if there is a deadlock process, only the deadlock process can be viewed. Of course, you can control through the parameters, whether there is a deadlock, only check the view lock process.

Copy Code code as follows:

Create proc 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 deadlock process, display normal process information, 1 display, 0 does not show
As
DECLARE @count int,@s nvarchar (1000), @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, username =loginame, Cumulative CPU time =CPU,
Login time =login_time, open transaction number =open_tran, process state =status,
Workstation name =hostname, application name =program_name, workstation process id=hostprocess,
Domain =nt_domain, network card 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 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 (1,1), a nvarchar (30),
b int,eventinfo nvarchar (255))
If @kill_lock_spid =1
Begin
DECLARE @spid varchar (10), @ Logo varchar (10)
While @i<= @count
Begin
Select @spid = Process id,@ flag = flag from #t where id=@i
Insert #t1 EXEC (' DBCC INPUTBUFFER (' + @spid + ') ')
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)
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

EXEC P_lockinfo