Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Deadlocked SQL Agent job Expand / Collapse
Author
Message
Posted Wednesday, December 5, 2012 9:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:39 PM
Points: 125, Visits: 1,048
Hi,

I have a SQL 2000 Agent job that executes a stored procedure which populates a table from a file.
The code in the SP is not within a transaction and the EXEC of the proc is also not in a transaction. It is just a SQL Agent job.
It happens every now and again that this job fails with an error message indicating that it was a deadlock victim.

My question is:
Will the queries in the proc roll back when this deadlock occurs?
OR
Will it import the file up to a the point of deadlock and then just stop - meaning an incomplete import?

Post #1393090
Posted Wednesday, December 5, 2012 10:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 11:46 PM
Points: 42, Visits: 290
Hello Casper,

According to my knowledge of deadlocks,

Deadlocking occurs when two or more user processes have locks on separate objects and each process is trying to acquire a lock on the object that the other process has locked. When this happens, SQL Server resolves the deadlock by automatically aborting one process, the "victim" process, allowing the other processes to continue.

The aborted transaction is rolled back and an error message is sent to the user of the aborted process. Generally, the transaction that requires the least amount of overhead to roll back is the transaction that is aborted.

So, theoritically, in your case, the table should not be populated at all.

Please refer to the below articles on handling deadlocks:

http://sqlserverpedia.com/wiki/Handling_Deadlock_Conditions_with_TRY_/_CATCH
http://www.simple-talk.com/sql/database-administration/handling-deadlocks-in-sql-server/

Let me know if it solves your query...
Post #1393121
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse