Database deadlocks

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

Creating an XEvents session using the SQL Server Management Studio wizard:

  1. Launch the New Session Wizard from Object Explorer Management/Extended Events/Sessions:
  2. Specify a name for your session. Select the Start the event session at server startup option if you want to launch it on server start:
     
  3. In the Select Events To Capture section, look for "xml_deadlock_report":
  4. In the Specify Session Data Storage section, specify the path to the location where the file containing the recorded session results will be stored. The Enable file rollover option enables automatic rotation in the specified directory.  
  5. In the Summary section, you may open and save the wizard session results in T-SQL.

Viewing information about deadlocks

  • Viewing information about deadlocks using the XEvents session
    In order to read *.xel files, SQL Server has a DMF sys.fn_xe_file_target_read_file component. Run the following query in SQL Server Management Studio and specify 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_graphfrom 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);
  • Viewing information about deadlocks using the system_health session
    If deadlock logging is not enabled, you can view information about deadlocks using a session that is always enabled by default (system_health).
    To do this, 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_graphfrom sys.fn_xe_file_target_read_file('system_health*.xel',null,null,null) as fcross apply (select cast(f.event_data as xml)) as e(xdata)where object_name = N'xml_deadlock_report';
  • Viewing information about deadlocks in Azure
    Azure has a separate session that is collected in blobstorage by default. To view it, use the sample query below (the query should be run using 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_graphfrom sys.fn_xe_telemetry_blob_target_read_file('dl', null, null, null) as fcross apply (select cast(f.event_data as xml)) as e(xdata);

Contact technical support and provide the deadlock information that you have gotten as a result of running the query.

10/9/2020 8:50:42 AM


Please leave your feedback about this article