0

I'm currently catching deadlock events with the profiler. (deadlock, deadlock graph, deadlock chain).

So far so good, but for this, a client computer needs to be constantly running and online.

==> Is there a way to catch & collect deadlock events as a background job directly on SQL Server?

2 Answers 2

6

Deadlock events are captured by the system_health Extended Events trace so you don't need to run Profiler interactively or create a SQL Trace to capture the information.

Below is an example query to retrieve the deadlock info from the file target:

--Get xml_deadlock_report events from system_health session file target
WITH
      --get trace folder path and append session name with wildcard (assumes base file name is same as session name)
      all_trace_files AS (
        SELECT path + '\system_health*.xel' AS FileNamePattern
        FROM sys.dm_os_server_diagnostics_log_configurations
        )
      --get xml_deadlock_report events from all system_health trace files
    , deadlock_reports AS (
        SELECT CAST(event_data AS xml) AS deadlock_report_xml
        FROM all_trace_files
        CROSS APPLY sys.fn_xe_file_target_read_file ( FileNamePattern, NULL, NULL, NULL) AS trace_records
        WHERE trace_records.object_name like 'xml_deadlock_report'
    )
SELECT TOP 10
      deadlock_report_xml.value('(/event/@timestamp)[1]', 'datetime2') AS UtcTimestamp
    , deadlock_report_xml AS DeadlockReportXml
FROM deadlock_reports;
2

Another option to use system_health is to use a dedicated XE session for deadlocks. It allow you to decide how much data you want to store, etc. Here's an example for such an XE session:

CREATE EVENT SESSION [Deadlocks] ON SERVER 
ADD EVENT sqlserver.xml_deadlock_report(
    ACTION(sqlserver.database_name))
ADD TARGET package0.event_file(SET filename=N'R:\Deadlocks',max_file_size=(1024),max_rollover_files=(3))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

ALTER EVENT SESSION [Deadlocks] ON SERVER 
 WITH (STARTUP_STATE=ON)

ALTER EVENT SESSION Deadlocks 
ON SERVER
STATE = START

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.