SQL Server Deadlock

Question

My scheduled job is working 6 times in a day. Sometimes its failing cause of deadlock. I tried to identify whose blocking my session.

I searched and i discovered sql profiler but its not showing exact result. How to identify historical with T-SQL or any other way ?

When the fail job error message shown belown,

transaction (process id ) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. rerun the transaction.

Answer 1

This should help identify deadlock victims or causes of deadlocking: https://ask.sqlservercentral.com/questions/5982/how-can-i-identify-all-processes-involved-in-a-dea.html

If you want to reduce the risk of your process deadlocking here are some strategies...

• Try to INSERT/UPDATE/DELETE tables in the same order. E.g. if one process is doing this:

  BEGIN TRAN; UPDATE TableA; UPDATE TableB; COMMIT;
  

while another process is doing this:

    BEGIN TRAN; UPDATE TableB; UPDATE TableA; COMMIT;

there is a risk that one process will deadlock the other. The greater the time to complete, the higher the risk of deadlock. SQL Server will simply choose one process as the "Deadlock Victim" at random.

• Try to minimise the code involved in a transaction. I.e. Fewer lines of INSERT/UPDATE/DELETE code between your BEGIN TRANSACTION and COMMIT TRANSACTION statements
• Process smaller batches of data if possible. If you are processing a large number of rows, try to add batching, so the code locks smaller batches of data at any given time.