To view the process that caused the deadlock in SQL Server

Create proc Sp_lockinfo
@kill_lock_spid Bit=1,--whether to kill the blocking process, 1 to kill, 0 to show only
@show_spid_if_nolock Bit=1,--if there is no blocked process, the normal process information is displayed, 1 is displayed, 0 is not displayed
@dbname sysname= "--if empty, queries all libraries, if NULL, queries the current library, otherwise queries the specified library
As
SET NOCOUNT ON
DECLARE @count int,@s nvarchar (4000), @dbid int
If @dbname = ' Set @dbid =db_id () Else set @dbid =db_id (@dbname)

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 (
The SELECT flag = ' blocked 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
where blocked>0
and (@dbid is null or [email protected])
Group by blocked
) B on a.spid=b.blocked
where a.blocked=0
and (@dbid is null or [email protected])
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=spid
From Master. sysprocesses A
where blocked<>0
and (@dbid is null or [email protected])
) A ORDER by s1,s2

Select @[email Protected] @rowcount

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
Where @dbid is null or [email protected]
ORDER BY SPID
Set @[email protected] @rowcount
End

If @count >0
Begin
CREATE table #t1 (ID int identity), a nvarchar (int,eventinfo), b nvarchar (900))
DECLARE TB cursor Local
For
Select N ' Insert #t1 exec (' DBCC INPUTBUFFER (' +rtrim (process id) + ') ')
If @ @rowcount =0 insert #t1 (a) VALUES (NULL)
' +case when @kill_lock_spid =1 and flag =n ' blocked process '
Then ' Kill ' +rtrim (process id) Else ' end
From #t
Open TB
Fetch TB into @s
While @ @fetch_status =0
Begin
EXEC (@s)
Fetch TB into @s
End
Close TB
Deallocate TB
Select a.*, the SQL statement for the process =b.eventinfo
From #t a join #t1 B on a.id=b.id
ORDER BY a.ID
End
SET NOCOUNT OFF

To view the process that caused the deadlock in SQL Server