Collecting Deadlock Information

Kendra Little has scripts to collect deadlock graph information using extended events or a server-side trace:

Choose the script that works for you. You can:

  1. Use a simple Extended Events trace to get deadlock graphs via the sqlserver.xml_deadlock_report event

  2. Use a Server Side SQL Trace to get deadlock graphs (for older versions of SQL Server, or people who like SQL Trace)

  3. Use a (much more verbose) Extended Events trace to get errors, completed statements, and deadlock graphs. You only need something like this if the input buffer showing in the deadlock graph isn’t enough, and you need to collect the other statements involved in the transactions. You do this by matching the transaction id for statements to the xactid for each item in the Blocked Process Report. Warning, this can generate a lot of events and slow performance.

I’d default to script #1 and look at #3 in extreme scenarios.

Related Posts

Azure SQL Database and Extended Events

Dave Bland shows how to set up and read an extended event file on Azure SQL Database: This first step when using T-SQL to read Extended Files that are stored in an Azure Storage Account is to create a database credential.  Of course the credential will provide essential security information to connect to the Azure […]

Read More

Safely Dropping Extended Event Sessions

Denis Gobo shows how you can drop an extended event session without risk of a “does not exist” error: The other day someone checked in some code and every now and then the build would fail with the error Msg 15151, Level 16, State 19, Line 51Cannot drop the event session ‘ProcsExecutions’, because it does […]

Read More

Categories

December 2016
MTWTFSS
« Nov Jan »
 1234
567891011
12131415161718
19202122232425
262728293031