I have a job scheduled to run every 3 hours for some data copying between databases, and every 02:00am run it is also running some extra calculations.
The problem is that every couple weeks, the 2am run will fail for no apparent reason citing a deadlock:
Executed as user: SQLserver\Administrator. Generated flat file - OK [SQLSTATE 01000] (Message 0) COPY SUCCEEDED [SQLSTATE 01000] (Message 0) Transaction (Process ID 77) was deadlocked on thread | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001] (Error 1205). The step failed.
So I went ahead and created a profiler job that will run every night at 01:55am for 15 minutes and hopefully catch the deadlock.
I now have a trace with EventClass=Deadlock Graph and I have exported that event to a XDL file and attached it here as a .txt file.
The problem is that I have no idea where to go from here with analyzing the trace file and understanding why the deadlock is happening. any help would be great.