Hi experts,
This instance encounters a transaction deadlock every day at a certain time. No jobs run at that time so it's coming from somewhere in the app or the user side.
I have viewed the Deadlock Graph. My question is how can I determine the true cause of the deadlock? Can I put something in place just before the time when the deadlock occurs? Thanks for any ideas.
You could try to run a trace just before the lock and stop it after
If you have an extended event that captures deadlocks, including the deadlock graph, view the XML data from that event, it will give you the queries involved in the deadlock.
If you don't have an extended event that captures deadlocks, set one up :-).
The system_health session captures the xml_deadlock_report event. Just need to parse it before the files or ring buffer roll over.
I prefer a separate one. I've found that on occasion the system health session can get a little crowded.