Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Deadlocked SQL Agent job Expand / Collapse
Posted Wednesday, December 5, 2012 9:38 AM


Group: General Forum Members
Last Login: Thursday, August 18, 2016 6:55 AM
Points: 131, Visits: 1,200

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?
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: Saturday, July 16, 2016 11:59 AM
Points: 42, Visits: 299
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:

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

Add to briefcase

Permissions Expand / Collapse