Hi experts, this is SQL Server 2012.
I have GOT to get a handle on these deadlocks. I turned on trace flag 1222
DBCC TRACEON (1222, -1). And I see, in the Sql Logs, several deadlocks have occurred within the past 2 hours. The logs are useful to a point.
How can I write the deadlock info to a table so I can see the SQL code for both the victim and the other that succeeded?.
Thanks.
Using an older technique, I used a Profiler Trace. Collected only the DeadLock event textData and Login
Pushed the results to a XML Graph
I see the graph now for each deadlock but no TSQL stmts at all.
How can I get these kinds of details and really I want the TSQL details for victim and the winning process to go to a table
Thanks
Suggest you take a look at Jonathan's material on deadlocks (if you haven't already):
More here: SQL Server Deadlocks
Youtube:
It's not necessary to enable the trace flags for deadlocks anymore, they are captured by the default trace and the system_health extended event. Either of those will be easier to work with than parsing the trace flag output from the error log.
It's worth your time to study the deadlock extended events and set one up for your specific situation, you can filter for deadlocks on specific databases, tables, etc. Jonathan's extended events series will give you the details of how.
Wayne Sheffield also has a nice query I've used in the past:
The output is a little verbose and you may have to tweak it depending on your XE target (he's commented a few of them out in his code, just uncomment for yours)