Detecting Deadlocks with SQL Server

We were helping a customer with a suspected deadlock problem, which turned out not to be occurring in TechDoc but with another application. Nonetheless, the process involves a few steps and we thought it might be helpful to document the process in case someone else might need to debug a SQL Server deadlock in the future.

1. Open SSMS (SQL Server Management Studio) and connect to the database server where the database resides
2. On the side menu, open Management > Extended Events > Sessions > system_health
3. Right click on package0.event_file and click on View Target Data...
4. When that comes up, hopefully there are a lot of events
5. To narrow it down, click on Filters... up on the toolbar
6. Check the box on Filter on event name if it's not already checked
7. Click on the Click here to add a clause message
8. For the Field, choose name in the drop down
9. Leave the Operator as =
10. For the Value, enter xml_deadlock_report
11. Click OK to apply the filter
12. If there's still lots of events, you may have to go back to the filter and add a Field of database_id and a Value of whatever the database ID is

Now, you may have one of more xml_deadlock_report events listed. If not, there were no deadlocks or it's been too long. The Extended Events are kept in a ring buffer that is overwritten as time goes by.

1. Click on a deadlock event to select it
2. Down below, details of that deadlock will be shown
3. Click on the Deadlock tab
4. In the center, there should be two Key Locks shown
5. Look at the object name one of the key locks to verify that it is a lock in the database that we are looking for
6. If not, click on the next deadlock and go back to step 2
7. If it is a deadlock, click back on the Details tab
8. In the Details tab, there should be a Field named xml_report
9. Double click on that field, and the actual XML report should open
10. You can now save that XML report and provide it for evaluation of the deadlock