SQL Server methods for viewing and resolving deadlocks

Create a new stored procedure in the master database-process Deadlocks-view the current process, or deadlock process, and automatically kill the dead process-because it is for dead, so if there is a deadlock process, you can only view the deadlock process--of course, you could control the parameters, whether there is no deadlock, The look dead lock process is only checked-call example exec p_lockinfocreate proc [dbo]. [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, whether to display normal process information, 1 display, 0 does not show asdeclare @count int,@s nvarchar (+), @i intselect id=identity (int,1,1), flags, 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 name =nt_domain, nic address =net_addressinto #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=0from Master. sysprocesses a join (select blocked from master: sysprocesses GROUP by blocked) B on a.spid=b.blocked where a.blocked=0union allselect ' |_ victim _> ', spid,kpid,blocked, Dbid,uid,loginame,cpu,login_time,open_tran,status,hostname,program_name,hostprocEss,nt_domain,net_address,s1=blocked,s2=1from Master. sysprocesses a where blocked<>0) a order by S1,s2select @[email protected] @rowcount, @i=1if @count =0 and @show_ Spid_if_nolock=1begininsert #tselect 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_addressfrom Master. Sysprocessesset @[email protected] @rowcountendif @count >0begincreate table #t1 (id int identity (), a nvarchar (+), b int,eventinfo nvarchar (255)) If @kill_lock_spid =1begindeclare @spid varchar (10), @ Flag varchar () while @i <[email protected]beginselect @spid = Process id,@ flag = flag from #t where [Email protected]insert #t1 exec (' DBCC InputBuffer (' [email protected]+ ') ') if @ flag = ' deadlock process ' exec (' kill ' [email protected]) set @[email  Protected]+1endendelsewhile @i<[email protected]beginselect @s= ' dbcc INPUTBUFFER (' +cast (process ID as varchar) + ') ' From #t where [Email protected]insert #t1 exec (@s) set @[emAil protected]+1endselect a.*, the SQL statement for the process =b.eventinfofrom #t a join #t1 B on A.id=b.idendgo execute the following stored procedure in the SQL query window exec p_lo Ckinfo

　　

SQL Server methods for viewing and resolving deadlocks