The source of the problem wasn’t immediately discernible as there wasn’t any pattern to when the job was failing. Troubleshooting was further complicated by the database being written/maintained by a 3rd party vendor that encrypts all of their stored procedures.
So… How to find out what was causing the deadlock?
Extended Events are an ideal solution for this situation.
Creating a new Extended Event session:
- In SQL Server Management Studio (SSMS), expand the Management and Extended Events folders, right-click on Sessions and select New Session.
- On the General page, enter a descriptive Session name, and select the ‘Start the event session at server startup‘ and ‘Start the event session immediately after session creation‘ options.
- On the Events page, add the following events from the Event library (on the left) to the list of Selected events (on the right).
- Click the Configure button to select the Global Fields (Actions) to be recorded for each event.
- On the Data Storage page select the event_file target type to write the event data to a file.
- Click on OK to close the window and begin data collection.
To view the Extended Event session log:
- Expand the Deadlocks session, right-click on the event file, and select View Target Data.
- Scroll to the bottom of the event list, select the most recent xml_deadlock_report or database_xml_deadlock_report, and double-click on the xml_report in the Details section. This will open the xml report in a new tab.
The xml report will show which actions are causing the deadlock. In this case, the report’s process-list shows two stored procedures (Alert.MissingLog and Collector.LogInfoUpdate). Looking further down, the resource-list shows that the Collector process has an exclusive lock on the object HVAC.Collector.LogDetails, and the Alert process is requesting a shared lock on the same table.
The last step is to send this information to the developers (since both stored procedures are encrypted) for further troubleshooting & remediation.