SQL Job having issues with transaction log...

Question

I have a SQL job that simply executes a stored procedure. Each morning when the job attempts to run, I get the following error:

The current transaction cannot be committed and cannot support operations that write to the log file.

When I keep trying to restart the job, it keeps giving me that same error. However, if I just execute the store procedure without using the Job it works fine.

And here's the really really tricky part. If I just run the stored procedure, cancel it, and then run the Job, the job works perfectly fine.

Has anyone come across this rather unique problem or have any ideas what may be causing it?

Answer 1

This error indicates that you are trying to do a logged operation during a doomed transaction. This can only happen in a BEGIN CATCH block if you're ignoring the XACT_STATE value of -1:

The current request has an active user transaction, but an error has occurred that has caused the transaction to be classified as an uncommittable transaction. The request cannot commit the transaction or roll back to a savepoint; it can only request a full rollback of the transaction. The request cannot perform any write operations until it rolls back the transaction. The request can only perform read operations until it rolls back the transaction. After the transaction has been rolled back, the request can perform both read and write operations and can begin a new transaction.

The fact that your trying to do this merely indicates a code problem with your exception handling (in other words your procedure is buggy). I have recently blogged about a template for procedures that use BEGIN TRY/BEGIN CATCH and you can use that as a starting point to fix your procedure. Erland Sommarskog has a well known article on Transact-SQL error handling, but that does not cover the BEGIN TRY/BEGIN CATCH too deeply.

With proper error handling in place you can then find out the original error that occurs and causes your CATCH block to be executed in the first place. Since you mention that running the procedure manually causes no issues then the problem is likely the differences in context between the SQL Agent job and your manual execution. I cannot diagnose the problem without any data, but my guess about the most likely cause is the difference in the security context (ie. the Agent login is lacking some rights your own login has).