SQL Server view deadlock and kill deadlock process for tables

Query out

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

Kill spid

Other:

EXEC Master.dbo.sp_who_lock--View the current deadlock process

EXEC master.dbo.p_killspid ytsafety--Kill the process that caused the deadlock

Sp_who_lock as follows:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS on
GO

CREATE PROCEDURE Sp_who_lock
As
Begin
declare @spid int, @bl int,
@intTransactionCountOnEntry int,
@intRowcount int,
@intCountProperties int,
@intCounter int

CREATE TABLE #tmp_lock_who (
ID int identity (+),
SPID smallint,
BL smallint)

IF @ @ERROR <>0 RETURN @ @ERROR

Insert into #tmp_lock_who (SPID,BL) 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

IF @ @ERROR <>0 RETURN @ @ERROR

--Find the number of records for the staging 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

--Cycle start
While @intCounter <= @intCountProperties
Begin
--Take the first record
Select @spid = spid, @bl = bl
From #tmp_lock_who where Id = @intCounter
Begin
If @spid =0
Select ' Causes the database deadlock: ' + CAST (@bl as VARCHAR (10) + ' process number, which executes the SQL syntax as follows '
Else
Select ' Process number spid: ' + cast (@spid as varchar (10)) + ' by ' + ' process number spid: ' + cast (@bl as varchar (10) + ' block, whose current process executes SQL syntax as follows ')
DBCC InputBuffer (@bl)
End

--The loop pointer moves down
Set @intCounter = @intCounter + 1
End

drop table #tmp_lock_who

return 0
End

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS on
GO

P_killspid as follows:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS on
GO

Create proc P_killspid
@dbname varchar (200)--the database name of the process to be closed
As
declare @sql nvarchar (500)
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 into @spid
While @ @fetch_status =0
Begin
EXEC (' kill '[email protected])
FETCH NEXT from #tb into @spid
End
Close #tb
Deallocate #tb

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS on
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS on
GO

ALTER proc P_killspid
@dbname varchar (200)--the database name of the process to be closed
As
declare @sql nvarchar (500)
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 into @spid
While @ @fetch_status =0
Begin
EXEC (' kill '[email protected])
FETCH NEXT from #tb into @spid
End
Close #tb
Deallocate #tb

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS on
GO

SQL Server view deadlock and kill deadlock process for tables