CODEWITHSUNDEEP
sqlsql-servertransaction-log
Justin Balvanz
Justin Balvanz

Reputation: 1186

SQL Job having issues with transaction log

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?

Upvotes: 1

Views: 13641

Answers (2)

Mike
Mike

Reputation: 11

I was getting this error under different conditions and I came-up with a short replication path (the software defect is missing IF XACT_STATE() != 1 ROLLBACK):

-- prepare SP

IF OBJECT_ID(N'ShortReplicationPath',N'P') IS NOT NULL     DROP PROCEDURE dbo.ShortReplicationPath
GO

CREATE PROCEDURE dbo.ShortReplicationPath
AS 

BEGIN  
    BEGIN TRY   

        insert #TempTabDateTime (ValidFrom) Values ('date')

    END TRY

    BEGIN CATCH

        PRINT ERROR_NUMBER();        PRINT ERROR_MESSAGE();

    INSERT INTO     #TempTabVarChar
    (       Text    )
    VALUES  (       'abcdefg'   )

    END CATCH

END 

GO

-- Execute test:

IF OBJECT_ID ('TEMPDB..#TempTabDateTime') IS NOT NULL drop table #TempTabDateTime

create table #TempTabDateTime (ValidFrom DATETIME)

IF OBJECT_ID ('TEMPDB..#TempTabVarChar') IS NOT NULL drop table #TempTabVarChar

create table #TempTabVarChar (Text VarChar(MAX))

BEGIN TRANSACTION

    EXEC dbo.ShortReplicationPath
ROLLBACK

GO

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294387

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).

Upvotes: 3

Related Questions