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 |
|
dim
Yak Posting Veteran
57 Posts |
 Posted - 2010-12-20 : 10:47:14
|
| Hi,I have a SQL server job that comprises of three steps and is schedule to run the job every night and it keeps failing at last step with the error :sg 1205, Sev 13, State 52, Line 225 : Transaction (Process ID 294) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001]The error message is taken from the log file for this job. Basically at this step it is trying to update the destination table with lots of records and probably failing to do so. The step at which the job fails is execution of a stored procedure. Please let me know where could I start with debugging the job to set it right. What might be the possible reason for this sp to stop running. The dba's tell that there are no other processes running at the same time . Please advice.Thank you,Dp |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-12-20 : 10:53:09
|
| For Agent jobs that can suffer from deadlocks, we add retry steps.To debug this, you'll need to determine who the owner of the deadlock is. You can do this by adding a trace flag or by viewing the deadlock graph in Profiler.For the trace flag then, you can run this for the current session of SQL Server:DBCC TRACEON(1222,-1)In case you want to track deadlocks after SQL gets restarted sometime in the future, have the DBA also add -T1222 to the startup parameters for SQL Server.The deadlock output using the trace flags will be put into the SQL Server Error Log. It'll be rather confusing to read, so have your DBA help you with it.You should also consider READ_COMMITTED_SNAPSHOT isolation level, especially if the deadlock owner is found to be a read-only query.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
dim
Yak Posting Veteran
57 Posts |
Posted - 2010-12-20 : 13:30:34
|
| Hi tkizer,I did have my dba look at the profiler and do as you suggested. I have SQL profiler results from Trace Start and Trace Stop eventclass. It says lock:Deadlock Chain. In one of the lines description in the textdata it says: Parallel query worker thread was involved in a deadlock. Can you advice which part of the query might casue this. Thank you,Dp |
|
|
|
dim
Yak Posting Veteran
57 Posts |
Posted - 2010-12-20 : 14:24:25
|
| The query is trying to do an update on a table. Would that mean applying no lock on that table? would that not allow bad updated data in the table?Dp |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dim
Yak Posting Veteran
57 Posts |
Posted - 2010-12-20 : 15:02:09
|
| Hi tkizer,Thank You for the response. The query that might be causing deadlock to occur is as below:UPDATE FIC.dbo.ContactSET contact_Updated_ind = 'Y' , datetime_updated = getdate() , last_action_cd = 'U' , last_program = 'Differential' , last_source_system = 'ix' , user_updated = USERFROM FIC.dbo.Contact C INNER JOIN WorkStaging_Ebix_Address_Type WA ON WA.Contact_ID = C.Contact_ID INNER JOIN FIC.dbo.Address_Type A ON WA.Contact_ID = A.Contact_ID AND WA.Address_Type = A.Address_TypeWHERE WA.Contact_ID IS NOT NULL AND WA.Address_ID IS NOT NULL AND WA.Address_Type_ID IS NULLWould using cursors to update records by row level help?Dp |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
SQL Job -Stored Procedure Failing