Database deadlocks

If SQL database deadlocks occur while you are using the program, enable deadlock information logging using the Extended Events system.

Create an XEvents session

Create the session with the SQL Server Management Studio wizard:

Launch the New Session Wizard from Object Explorer under Management → Extended Events → Sessions.
Specify a name for your session. To launch it on server start, select the Start the event session at server startup option.
In the Select Events To Capture section, look for xml_deadlock_report.
In the Specify Session Data Storage section, specify the path to the location where the file containing the recorded session results is stored. The Enable file rollover option enables automatic rotation in the specified directory.
In the Summary section, you can open and save the wizard session results in T-SQL.

View deadlock information

From the XEvents session

To read .xel files, SQL Server provides the sys.fn_xe_file_target_read_file table-valued function. Run the following query in SQL Server Management Studio, specifying the path to the trace files:

select
    xdata.value('(/event/data/value/deadlock/process-list/process/@lasttranstarted)[1]', 'datetime')   as lasttranstarted,
    xdata.value('(/event/data/value/deadlock/resource-list/keylock/@objectname)[1]', 'nvarchar(max)')  as objectname,
    xdata.value('(/event/data/value/deadlock/process-list/process/@clientapp)[1]', 'nvarchar(256)')    as client_app,
    xdata.value('(/event/data/value/deadlock/process-list/process/@hostname)[1]', 'nvarchar(256)')     as hostname,
    xdata.value('(/event/data/value/deadlock/process-list/process/@loginname)[1]', 'nvarchar(256)')    as loginname,
    xdata.value('(/event/data/value/deadlock/resource-list/keylock/@indexname)[1]', 'nvarchar(256)')   as indexname,
    xdata.value('(/event/data/value/deadlock/process-list/process/@waittime)[1]', 'int')               as waittime,
    xdata.value('(/event/data/value/deadlock/resource-list/keylock/@mode)[1]', 'nvarchar(10)')         as lockmode,
    xdata.query('(/event/data/value/deadlock)[1]')                                                     as deadlock_graph
from sys.fn_xe_file_target_read_file('D:\MSSQL\XE\deadlocks\deadlocks*.xel', null, null, null) as f  -- wildcard (deadlocks*.xel)
cross apply (select cast(f.event_data as xml)) as e(xdata);

From the system_health session

If deadlock logging is not enabled, you can view deadlock information using the system_health session, which is always enabled by default. Run the following query in SQL Server Management Studio:

select
    e.xdata.value('(/event/data/value/deadlock/process-list/process/@lasttranstarted)[1]', 'datetime')   as lasttranstarted,
    e.xdata.value('(/event/data/value/deadlock/resource-list/keylock/@objectname)[1]', 'nvarchar(max)')  as objectname,
    e.xdata.value('(/event/data/value/deadlock/process-list/process/@clientapp)[1]', 'nvarchar(256)')    as client_app,
    e.xdata.value('(/event/data/value/deadlock/process-list/process/@hostname)[1]', 'nvarchar(256)')     as hostname,
    e.xdata.value('(/event/data/value/deadlock/process-list/process/@loginname)[1]', 'nvarchar(256)')    as loginname,
    e.xdata.value('(/event/data/value/deadlock/resource-list/keylock/@indexname)[1]', 'nvarchar(256)')   as indexname,
    e.xdata.value('(/event/data/value/deadlock/process-list/process/@waittime)[1]', 'int')               as waittime,
    e.xdata.value('(/event/data/value/deadlock/resource-list/keylock/@mode)[1]', 'nvarchar(10)')         as lockmode,
    e.xdata.query('(/event/data/value/deadlock)[1]')                                                     as deadlock_graph
from sys.fn_xe_file_target_read_file('system_health*.xel', null, null, null) as f
cross apply (select cast(f.event_data as xml)) as e(xdata)
where object_name = N'xml_deadlock_report';

In Azure

Azure has a separate session that is collected in blob storage by default. To view it, run the query below in the master context:

select
    f.file_name,
    xdata.value('(/event/data/value/deadlock/process-list/process/@lasttranstarted)[1]', 'datetime')   as lasttranstarted,
    xdata.value('(/event/data/value/deadlock/resource-list/keylock/@objectname)[1]', 'nvarchar(max)')  as objectname,
    xdata.value('(/event/data/value/deadlock/process-list/process/@clientapp)[1]', 'nvarchar(256)')    as client_app,
    xdata.value('(/event/data/value/deadlock/process-list/process/@hostname)[1]', 'nvarchar(256)')     as hostname,
    xdata.value('(/event/data/value/deadlock/process-list/process/@loginname)[1]', 'nvarchar(256)')    as loginname,
    xdata.value('(/event/data/value/deadlock/resource-list/keylock/@indexname)[1]', 'nvarchar(256)')   as indexname,
    xdata.value('(/event/data/value/deadlock/process-list/process/@waittime)[1]', 'int')               as waittime,
    xdata.value('(/event/data/value/deadlock/resource-list/keylock/@mode)[1]', 'nvarchar(10)')         as lockmode,
    xdata.query('(/event/data/value/deadlock)[1]')                                                     as deadlock_graph
from sys.fn_xe_telemetry_blob_target_read_file('dl', null, null, null) as f
cross apply (select cast(f.event_data as xml)) as e(xdata);

After you run the query, contact technical support and provide the deadlock information from the results.

Administrator's Guide

Administration Guide

Performance Guide

Troubleshooting

Cloud

Create an XEvents session

View deadlock information

From the XEvents session

From the system_health session

In Azure

​Create an XEvents session

​View deadlock information

​From the XEvents session

​From the system_health session

​In Azure

Create an XEvents session

View deadlock information

From the XEvents session

From the system_health session

In Azure