Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gangadhara.ms
Aged Yak Warrior
549 Posts |
 Posted - 2011-11-18 : 00:04:40
|
| Hi,One of my job which was running smooth now giving this error regularly.MessageExecuted 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. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-11-18 : 00:10:45
|
| Enable the deadlock trace flag (1222) globally and then wait for it to happen again. Once it does, post the deadlock output from the error log. And perhaps post the job step's code.Depending on what the job does, I typically add retries to the job step. These days though, I am rewriting code that is run via SQL Agent and that is prone to deadlock by adding TRY/CATCH logic and looking for error 1205 (deadlock error). I then retry within the code so that I don't have to start all over again at the job step level. But it really depends on your situation. The type of job that I'm talking about here is a purge job (massive deletes in batches).Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
|
|
|
Deadlock issue