SQL Server query and kill process deadlock statement _mssql

Query deadlock Process Statement

Select 
request_session_id spid, 
object_name (resource_associated_entity_id) tablename 
from 
sys.dm _tran_locks 
where 
resource_type= ' OBJECT '

Kill the deadlock process statement

Kill spid

Let's share a section about SQL Server detection deadlock; kill locks and processes; View lock information

--Detection of deadlocks--if a deadlock occurs, how do we detect which SQL statement or stored procedure is the specific deadlock? -then we can use the following stored procedures to detect, we can identify the process and the SQL statement that caused the deadlock.
SQL Server's own system stored procedures sp_who and sp_lock can also be used to find blocking and deadlocks, but the methods described here are not easy to use. Use master go CREATE PROCEDURE Sp_who_lock as BEGIN declare @spid int, @bl int, @intTransactionCountOnEntry int, @intRow count int, @intCountProperties int, @intCounter int CREATE TABLE #tmp_lock_who (ID int identity (1,1), spid Smallin  T, bl smallint) IF @ @ERROR <>0 return @ @ERROR Insert to #tmp_lock_who (SPID,BL) Select 0, blocked from (SELECT * From sysprocesses where blocked>0) a Where NOT EXISTS (SELECT * FROM (SELECT * from sysprocesses where blocked>0 b where a.blocked=spid) union select spid,blocked from sysprocesses where blocked>0 IF @ @ERROR <>0 return @@  ERROR--number of records found for temporary table Select @intCountProperties = count (*), @intCounter = 1 from #tmp_lock_who IF @ @ERROR <>0 return
@ @ERROR If @intCountProperties =0 select ' now has no blocking and deadlock information ' as Message--loop start while @intCounter <= @intCountPropertiesBegin--Take the first record select @spid = spid, @bl = bl from #tmp_lock_who where Id = @intCounter begin if @spid =0 select ' Cause database Deadlock is: ' + cast (@bl as VARCHAR (10)) + ' process number, its execution of the SQL syntax is as follows ' else select ' Process number spid: ' + cast (@spid as VARCHAR (10)) + ' + ' The process number spid: ' + CAST (@bl as VARCHAR (10)) + ' is blocked, and its current process executes the SQL syntax ' DBCC inputbuffer (@bl) End--the loop pointer moves down the set @intCounter = @int Counter + 1 End drop table #tmp_lock_who return 0 end-kill locks and processes-How to manually kill processes and locks? The simplest way to restart the service.
But here's an introduction to a stored procedure that kills processes and locks by explicit invocation. Use master go if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ P_killspid] and OBJECTPROPERTY (ID, N ' isprocedure ') = 1) drop procedure [dbo].
[P_killspid]
 Go create proc p_killspid @dbname varchar (200)--database name of the process to be closed as declare @sql nvarchar ($) Declare @spid nvarchar (20) Declare #tb cursor FOR select spid=cast (spid as varchar) from master. sysprocesses where dbid=db_id (@dbname) Open #tb fetch next from #tb to @spid while @ @fetch_status =0 begin EXEC (' K
 Ill ' + @spid) FETCH NEXT from #tb to @spid end close #tb deallocate #tb Go--usage exec p_killspid ' newdbpy '--View lock information--How to view the details of all locks in the system Fine information?
In Enterprise Management Manager, we can see some process and lock information, here is another method. --View lock information CREATE TABLE #t (req_spid int,obj_name sysname) declare @s nvarchar (4000), @rid int, @dbname sysname, @id int, @objna Me sysname declare TB cursor FOR SELECT DISTINCT req_spid,dbname=db_name (rsc_dbid), Rsc_objid from master. syslockinfo where Rsc_type in (4,5) open TB fetch NEXT from TB to @rid, @dbname, @id while @ @fetch_status =0 begin set @s= ' Select @objname =name from [' + @dbname + ']. sysobjects where id= @id ' exec sp_executesql @s,n ' @objname sysname out, @id int ', @objname out, @id inserts into #t values (@r ID, @objname) fetch next from TB to @rid, @dbname, @id end close TB deallocate TB Select process id=a.req_spid, Database =db_name (RSC _dbid), type =case rsc_type when 1 Then ' NULL resource (unused) ' When 2 Then ' database ' when 3 then ' file ' when 4 Then ' index ' when 5th En ' table ' when 6 then ' when 7 then ' key ' when 8 then ' the extents ' when 9 theN ' RID (row ID) ' When then ' application ' end, Object Id=rsc_objid, object name =b.obj_name, Rsc_indid from master. Syslockinfo a LEFT join #t b on a.req_spid=b.req_spid go drop table #t

The above is a small set to introduce SQL Server query and kill process deadlock statement, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!