SQL Server Blocking DBCC Inputbuffer Script

SELECT spid,dbid,DB_NAME(dbid),status,hostname,program_name,*


FROM MASTER.SYS.sysprocesses

WHERE blocked <>0

GO

SELECT 'DBCC INPUTBUFFER(' + CAST(spid AS VARCHAR) + ')' ,dbid,DB_NAME(dbid),status,hostname,program_name

FROM MASTER.SYS.sysprocesses

WHERE blocked <>0

GO

SELECT 'KILL ' + CAST(spid AS VARCHAR) + ';GO' ,dbid,DB_NAME(dbid),status,hostname,program_name

FROM MASTER.SYS.sysprocesses

WHERE blocked <>0

DBCC INPUTBUFFER(70)

Turn On Deadlock Trace

Reference:  http://sqlserverplanet.com/troubleshooting/turn-on-deadlock-trace-flag

Reference: http://www.sqlservercentral.com/articles/deadlock/65658/

Reference: http://www.sqlserver-training.com/how-to-trace-deadlock-in-sql-server/-

SQL Server Blocking Report using DMV

Blocking Information with details:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
GETDATE() as DateTime
,Waits.wait_duration_ms / 1000 AS WaitInSeconds
, Blocking.session_id as BlockingSessionId
, Sess.login_name AS BlockingUser
, Sess.host_name AS BlockingLocation
, BlockingSQL.text AS BlockingSQL
, Blocked.session_id AS BlockedSessionId
, BlockedSess.login_name AS BlockedUser
, BlockedSess.host_name AS BlockedLocation
, BlockedSQL.text AS BlockedSQL
, DB_NAME(Blocked.database_id) AS DatabaseName
FROM sys.dm_exec_connections AS Blocking
INNER JOIN sys.dm_exec_requests AS Blocked
ON Blocking.session_id = Blocked.blocking_session_id
INNER JOIN sys.dm_exec_sessions Sess
ON Blocking.session_id = sess.session_id
INNER JOIN sys.dm_tran_session_transactions st
ON Blocking.session_id = st.session_id
LEFT OUTER JOIN sys.dm_exec_requests er
ON st.session_id = er.session_id
AND er.session_id IS NULL
INNER JOIN sys.dm_os_waiting_tasks AS Waits
ON Blocked.session_id = Waits.session_id
CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle)
AS BlockingSQL
INNER JOIN sys.dm_exec_requests AS BlockedReq
ON Waits.session_id = BlockedReq.session_id
INNER JOIN sys.dm_exec_sessions AS BlockedSess
ON Waits.session_id = BlockedSess.session_id
CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS BlockedSQL
WHERE Waits.wait_duration_ms > 3000 -- This is 3 seconds.
--You can set it up based on your preferable seconds
ORDER BY WaitInSeconds

Lead Blocker Script for SQL Server

Find LeadBlocker from sysprocesses.

SELECT

spid
,sp.STATUS
,loginame = SUBSTRING(loginame, 1, 12)
,hostname = SUBSTRING(hostname, 1, 12)
,blk = CONVERT(CHAR(3), blocked)
,open_tran
,dbname = SUBSTRING(DB_NAME(sp.dbid),1,10)
,cmd
,waittype
,waittime
,last_batch
,SQLStatement =
SUBSTRING
(
qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
ELSE er.statement_end_offset
END - er.statement_start_offset)/2
)
FROM master.dbo.sysprocesses sp
LEFT JOIN sys.dm_exec_requests er
ON er.session_id = sp.spid
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked = 0