Geek asks and answers

SQL job with transaction log problems

I have an SQL job that just executes a stored procedure. Every morning, when the work tries to start, I get the following error:

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

When I keep trying to restart the task, it keeps giving me the same error. However, if I just perform the repository procedure without using a job, it works fine.

And the really really difficult part. If I just started the stored procedure, canceled it, and then started Job, the job works fine.

Does anyone encounter this rather unique problem or have any ideas what might cause it?

+1
source share
2 answers

This error indicates that you are trying to perform a registered operation during a doomed transaction. This can only happen in the BEGIN CATCH block if you ignore the XACT_STATE value :

There is an active transaction user in the current request, but an error occurred that caused the transaction to be classified as a non-local transaction. The request cannot complete a transaction or rollback to a savepoint; he can request only a complete rollback of the transaction. the request cannot record until it returns the transaction. A request can only perform read operations until it steers a transaction back. After the transaction has been rejected, the request can perform both reading and writing operations and can start a new transaction.

, , ( , ). , BEGIN TRY/BEGIN CATCH, . Erland Sommarskog Transact-SQL, BEGIN TRY/BEGIN CATCH.

, CATCH . , , , SQL . - , - (.. , ).

+3

, ( 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
+1

More articles:


All Articles