March 16, 2009 at 2:37 pm
Hi guys,
I have a package which runs every 10 mins, i am having a error message one or two times a day saying the update command was failed as you can see in the job history, and its saying to rerun the job as it has been a victim of deadlock.
Actually today i received this error 2 times, in one case it didnt updated the records but in the other message it updated the records but still i see this error message.
Does anyone know whats that bothering this stored procedure in the job to update.
Thanks
Here is the job History.
Date
LogJob History
Step ID1
ServerSERVER
Job Name
Step NameCRS5 RFD
Duration00:00:10
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: xyz\svcserver. ...n 9.00.1399.06 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 3:38:00 PM Progress: 2009-03-16 15:38:01.32 Source: Transform Data Task undefined Execute DTS 2000 Package Task is initiated: 0% complete End Progress Progress: 2009-03-16 15:38:02.11 Source: Transform Data Task undefined Execute DTS 2000 Package Task is completed: 100% complete End Progress Error: 2009-03-16 15:38:10.02 Code: 0xC002F210 Source: Update DE Dialer Execute SQL Task Description: Executing the query "--Update Queue table Exec UpdateCRS5_RFD" failed with the following error: "Transaction (Process ID 146) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connectio... Process Exit Code 0. The step succeeded.
March 17, 2009 at 7:29 am
can anyone help me in this issue,
March 17, 2009 at 7:37 am
I suggest you enable trace flag 1222 so the deadlock information is output to the error log, then you will be able to see what process the package is conflicting with. Then you can either modify the package or the other process to eliminate the deadlock.
See these resources:
http://sqlinthewild.co.za/index.php/2008/09/16/deadlock-graphs/
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 17, 2009 at 7:42 am
How do i enable the trace flag 1222?
Thanks
March 17, 2009 at 7:53 am
Check out the articles I included as they will help you understand deadlocks and how to troubleshoot and correct them.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 17, 2009 at 8:25 am
ALIF (3/17/2009)
How do i enable the trace flag 1222?Thanks
DBCC TRACEON(1222,-1)- Enabled the flag globally.
You get the graph in the Error log. copy it to a text file and post it here if you need any help. Try to resolve it yourselves any questions post it here.
check out this one as well:
http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
There are couple of steps to prevent deadlocking. The simplest method you can SEt deadlock priority low if you think one of those queries is insignificant than the other.
March 17, 2009 at 8:33 am
Thanks Krishna,
well before enabling the trace flag, i just want to know is there any impact of it to the sql server, if not i will enable it.
FYI
one thing is that, i got the error message of deadlock two times yesterday, first when it didnt updated the records which i reasonable that it says the process was a victim of deadlock, but another time when i checked the records they were updated but still i received this error. so i dont know whether its the deadlock is causing the issue?
Thanks
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply