在後台作業中擷取死鎖事件？

我目前正在使用分析器擷取死鎖事件。（死鎖、死鎖圖、死鎖鏈）。

到目前為止一切順利，但為此，客戶端電腦需要不斷執行並線上。

==> 有沒有辦法直接在 SQL Server 上作為後台作業擷取和收集死鎖事件？

死鎖事件由 system_health 擴展事件跟踪擷取，因此您無需以互動方式執行 Profiler 或創建 SQL 跟踪來擷取資訊。

以下是從文件目標檢索死鎖資訊的範例查詢：

--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;

引用自：https://dba.stackexchange.com/questions/281379