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.
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:
while another process is doing this:
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.