October 25, 2012 at 7:38 am
I got a job which runs daily having two steps
1. it truncates the present database.
2.It import the data from other resource into truncated database.
Some times it goes good but once in a while it throws an error regarding deadlock. I want this job to be completed at any cost every time. How can I set deadlock priority to this? Can i do that?
ERROR: Executed as user: . Transaction (Process ID 130) 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.
October 25, 2012 at 7:40 am
You can set its deadlock priority to high.
Or, if you want help in fixing it, switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.
DBCC TRACEON(1222,-1)
p.s. Immediate solutions can be required from people who you pay. We're all volunteers who post in their spare time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 25, 2012 at 8:25 am
If you're truncating the entire database, how about setting it to restricted user prior to running the process? If you need to you can even rollback outstanding connections immediately (with all that implies of course).
But, that assumes that the deadlock is from a second source. If you're getting deadlocks from your own data load process (due to parallelism or some part of your processing, hard to know), then this might not help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 25, 2012 at 8:29 am
stillconfused (10/25/2012)
...I want this job to be completed at any cost every time.
...
You can try this idea:
Make sure that no other session is using this database.
Take the database to restricted user mode or kill other sessions which are having lock in this database.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply