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

Tips When Writing Extended Events To Files

Jason Brimhall has some tips to help you use the file target in Extended Events: This first little tip comes from a painful experience. It is common sense to only try and create files in a directory that exists, but sometimes that directory has to be different on different systems. Then comes a little copy […]

Read More

Monitoring When Databases Go Offline

Jason Brimhall shows how you can create an extended event to track whenever databases go offline: The other day, I shared an article showing how to audit database offline events via the default trace. Today, I will show an easier method to both audit and monitor for offline events. What is the difference between audit and […]

Read More

Categories

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