Deadlock

 1
 2PRINT '-- Get current running commands';
 3-- current running commands
 4SELECT
 5      @@servername AS ServerName
 6    , GetDate() AS LocalDate
 7    , sder.session_id AS [SPID]
 8    , sdes.login_name AS [Login]
 9    , sd.name AS [DBName]
10    , sder.start_time AS [Start Time]
11    , sder.status AS [Status]
12    , sder.percent_complete AS [Pct Cmplt]
13    , sder.estimated_completion_time AS [Est Cmplt Time]
14    , sder.wait_type AS [Wait Type]
15    , sder.wait_time AS [Wait Time]
16    , sder.last_wait_type AS [Last Wait]
17    , sder.cpu_time AS [CPU Time]
18    , sder.total_elapsed_time AS [Total Elpsd Time]
19    , sder.reads AS [Reads]
20    , sder.writes AS [Writes]
21    , sder.logical_reads AS [Logical Reads]
22    , Stuff((
23    SELECT
24        ', ' + v.NAME
25    FROM       sys.dm_exec_plan_attributes(sder.plan_handle) AS pa
26    INNER JOIN master..spt_values  v  ON v.type = 'sop' -- Set OPtions I guess
27            AND v.number & Cast(pa.value AS INT) = v.number
28        WHERE
29            attribute =
30            'set_options'
31    FOR XML PATH('')
32    ), 1, 13, '') AS [Set Options]
33    , sder.command AS [Command]
34    , sdet.text AS [SQL Text]
35    , CASE WHEN SDER.[statement_start_offset] > 0 THEN
36        --The start of the active command is not at the beginning of the full command text
37        CASE SDER.[statement_end_offset] WHEN -1 THEN
38            --The end of the full command is also the end of the active statement
39            SUBSTRING(sdet.TEXT, (SDER.[statement_start_offset]/2) + 1, 2147483647)
40                                                 ELSE
41            --The end of the active statement is not at the end of the full command
42            SUBSTRING(sdet.TEXT, (SDER.[statement_start_offset]/2) + 1, (SDER.[statement_end_offset] - SDER.[statement_start_offset])/2) END
43                                                  ELSE
44        --1st part of full command is running
45        CASE SDER.[statement_end_offset] WHEN -1 THEN
46            --The end of the full command is also the end of the active statement
47            RTRIM(LTRIM(sdet.[text]))
48                                                 ELSE
49            --The end of the active statement is not at the end of the full command
50            LEFT(sdet.TEXT, (SDER.[statement_end_offset]/2) +1) END END AS [executing statement]
51FROM        sys.dm_exec_Requests                sder
52CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sdet
53JOIN        sys.dm_exec_sessions                sdes ON sder.session_id = sdes.session_id
54JOIN        sys.databases                       sd   ON sder.database_id = sd.database_id
55    WHERE
56        sder.session_id <> @@SPID

 1
 2-- !! Make sure this path exists before you start the trace !!
 3CREATE EVENT SESSION [blocked_process] ON SERVER
 4ADD EVENT sqlserver.blocked_process_report(
 5    ACTION(sqlserver.client_app_name,
 6           sqlserver.client_hostname,
 7           sqlserver.database_name)) ,
 8ADD EVENT sqlserver.xml_deadlock_report (
 9    ACTION(sqlserver.client_app_name,
10           sqlserver.client_hostname,
11           sqlserver.database_name))
12ADD TARGET package0.asynchronous_file_target
13(SET filename = N'c:\XEventSessions\blocked_process.xel',
14     metadatafile = N'c:\XEventSessions\blocked_process.xem',
15     max_file_size=(65536),
16     max_rollover_files=5)
17WITH (MAX_DISPATCH_LATENCY = 5SECONDS)
18GO

 1
 2/* Enabled to show advanced options */
 3EXEC sp_configure 'show advanced options', 1 ;
 4GO
 5RECONFIGURE ;
 6GO
 7/* Enabled the blocked process report */
 8EXEC sp_configure 'blocked process threshold', '5';
 9RECONFIGURE
10GO
11/* Start the Extended Events session */
12ALTER EVENT SESSION [blocked_process] ON SERVER
13STATE = START;

 1
 2DECLARE @xel nvarchar(1024);  -- log filename
 3DECLARE @xem nvarchar(1024);  -- Meatadata file
 4DECLARE @x   nvarchar(1024);  -- temp variable
 5SELECT  @x = CAST(sys.server_event_session_fields.value as nvarchar(1024))
 6FROM sys.server_event_session_fields INNER JOIN sys.server_event_sessions
 7ON sys.server_event_session_fields.event_session_id = sys.server_event_sessions.event_session_id
 8WHERE sys.server_event_session_fields.name = N'filename' AND sys.server_event_sessions.name = N'blocked_process'
 9SET @x = SUBSTRING(@x ,  1, LEN(@x)-4)
10SET @xem = @x+'*.xem';
11SET @xel = @x+'*.xel';
12
13WITH events_cte AS (
14    SELECT
15    xevents.event_data,
16    DATEADD(mi,
17    DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP),
18    xevents.event_data.value('(event/@timestamp)[1]', 'datetime2')) AS [event time] ,
19    xevents.event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(128)') AS [client app name],
20    xevents.event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(max)') AS [client host name],
21    xevents.event_data.value('(event[@name="blocked_process_report"]/data[@name="database_name"]/value)[1]', 'nvarchar(max)') AS [database name],
22    xevents.event_data.value('(event[@name="blocked_process_report"]/data[@name="database_id"]/value)[1]', 'int') AS [database_id],
23    xevents.event_data.value('(event[@name="blocked_process_report"]/data[@name="object_id"]/value)[1]', 'int') AS [object_id],
24    xevents.event_data.value('(event[@name="blocked_process_report"]/data[@name="index_id"]/value)[1]', 'int') AS [index_id],
25    xevents.event_data.value('(event[@name="blocked_process_report"]/data[@name="duration"]/value)[1]', 'bigint') / 1000 AS [duration (ms)],
26    xevents.event_data.value('(event[@name="blocked_process_report"]/data[@name="lock_mode"]/text)[1]', 'varchar') AS [lock_mode],
27    xevents.event_data.value('(event[@name="blocked_process_report"]/data[@name="login_sid"]/value)[1]', 'int') AS [login_sid],
28    xevents.event_data.query('(event[@name="blocked_process_report"]/data[@name="blocked_process"]/value/blocked-process-report)[1]') AS blocked_process_report,
29    xevents.event_data.query('(event/data[@name="xml_report"]/value/deadlock)[1]') AS deadlock_graph
30    FROM    sys.fn_xe_file_target_read_file (@xel, @xem, null, null)
31    CROSS APPLY (SELECT CAST(event_data AS XML) AS event_data) as xevents
32)
33SELECT
34    CASE WHEN blocked_process_report.value('(blocked-process-report[@monitorLoop])[1]', 'nvarchar(max)') IS NULL
35        THEN 'Deadlock'
36        ELSE 'Blocked Process'
37        END AS ReportType,
38    [event time],
39    CASE [client app name] WHEN '' THEN ' -- N/A -- '
40                            ELSE [client app name]
41                            END AS [client app _name],
42    CASE [client host name] WHEN '' THEN ' -- N/A -- '
43                            ELSE [client host name]
44                            END AS [client host name],
45    [database name],
46    COALESCE(OBJECT_SCHEMA_NAME(object_id, database_id), ' -- N/A -- ') AS [schema],
47    COALESCE(OBJECT_NAME(object_id, database_id), ' -- N/A -- ') AS [table],
48    index_id,
49    [duration (ms)],
50    lock_mode,
51    COALESCE(SUSER_NAME(login_sid), ' -- N/A -- ') AS username,
52    CASE WHEN blocked_process_report.value('(blocked-process-report[@monitorLoop])[1]', 'nvarchar(max)') IS NULL
53        THEN deadlock_graph
54        ELSE blocked_process_report
55        END AS Report
56FROM events_cte
57ORDER BY [event time] DESC ;