November 17, 2011 at 10:04 pm
Hi,
One of my job which was running smooth now giving this error regularly.
Message
Executed as user: AP\sys_aar. Transaction (Process ID 109) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001] (Error 1205). The step failed.
How can take precautions to avoid this,we can't reschedule this job to further, i need to identify which process is making the issue.
November 18, 2011 at 1:19 am
First you need to identify which two processes cause the deadlock. You can use profiler to trace the deadlock graph.
It's difficult to say how to solve this issue without knowing the queries involved, but in some cases better indexing can help. But then again you first need to know which tables are involved.
Here's a good article on how to track deadlocks and what you can do to avoid them:
[font="Verdana"]Markus Bohse[/font]
November 18, 2011 at 6:09 am
You can also enable traceflag 1222 which will output deadlock info to the error log in SQL Server whenever a deadlock occurs. This is frequently easier than trying to capture the deadlock using a trace. But, the trace output is easier to read.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply