SQL SERVER Query deadlock

Method One:

With Cte_sid (Bsid, SID, sql_handle)

As (SELECT blocking_session_id,

session_id,

Sql_handle

From sys.dm_exec_requests

WHERE blocking_session_id <> 0

UNION All

SELECT a.blocking_session_id,

A.SESSION_ID,

A.sql_handle

From Sys.dm_exec_requests A

JOIN cte_sid B on a.session_id = B.bsid

)

SELECT C.bsid,

C.sid,

S.login_name,

S.host_name,

S.status,

S.cpu_time,

S.memory_usage,

S.last_request_start_time,

S.last_request_end_time,

S.logical_reads,

S.row_count,

Q.text

From Cte_sid C

JOIN sys.dm_exec_sessions S on c.sid = s.session_id

Cross APPLY sys.dm_exec_sql_text (c.sql_handle) Q

ORDER by SID;

Method Two:

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 (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 to @spid, @bl;

While @ @FETCH_STATUS = 0

BEGIN

IF @spid = 0

SELECT ' causes the database deadlock: ' + CAST (@bl as VARCHAR ())

+ ' process number, which executes the SQL syntax as follows ';

ELSE

SELECT ' process number spid: ' + CAST (@spid as VARCHAR) + ' by ' + ' process number spid: '

+ CAST (@bl as VARCHAR) + ' block, and its current process executes the following SQL syntax ';

DBCC InputBuffer (@bl);

FETCH NEXT from S_cur to @spid, @bl;

END;

CLOSE s_cur;

Deallocate s_cur;

SQL SERVER Query deadlock