SQL Transaction Error The current transaction cannot be committed and cannot support operations that write to the log file

Question

I m having a similar issue to The current transaction cannot be committed and cannot support operations that write to the log file  but I have a follow-up question   The answer there references Using TRY   CATCH in Transact-SQL  which I ll come back to in a second     My code  inherited  of course  has the simplified form   SET NOCOUNT ON SET XACT ABORT ON  CREATE TABLE  tmp  SET  transaction    insert backtest results  BEGIN TRANSACTION  transaction  BEGIN TRY      --do some bulk insert stuff into  tmp  END TRY  BEGIN CATCH     ROLLBACK TRANSACTION  transaction     SET  errorMessage    bulk insert error importing results for backtest             CAST  backtest id as VARCHAR               check backtestfiles  directory for error files                error number      CAST ERROR NUMBER   AS VARCHAR               error message      CAST ERROR MESSAGE   AS VARCHAR 200               error severity      CAST ERROR SEVERITY   AS VARCHAR              error state      CAST ERROR STATE   AS VARCHAR               error line      CAST ERROR LINE   AS VARCHAR      RAISERROR  errorMessage  16  1      RETURN -666 END CATCH  BEGIN TRY      EXEC usp other stuff 1  whatever      EXEC usp other stuff 2  whatever      -- a LOT of  normal  logic here    inserts  updates  etc     END TRY  BEGIN CATCH      ROLLBACK TRANSACTION  transaction     SET  errorMessage    error importing results for backtest             CAST  backtest id as VARCHAR              error number      CAST ERROR NUMBER   AS VARCHAR               error message      CAST ERROR MESSAGE   AS VARCHAR 200               error severity      CAST ERROR SEVERITY   AS VARCHAR              error state      CAST ERROR STATE   AS VARCHAR               error line      CAST ERROR LINE   AS VARCHAR      RAISERROR  errorMessage  16  1      RETURN -777  END CATCH  RETURN 0   I think I have enough information to just play with it and figure it out myself    unfortunately reproducing the error is proving damn near impossible  So I m hoping that asking here will help clarify my understanding of the problem and solution   This stored procedure is  intermittently  throwing errors like this one      error importing results for backtest 9649 error number  3930 error message  The current transaction cannot be committed and cannot support operations that write to the log file  Roll back the transaction  error severity  16 error state 1 error line  217   So obviously the error is coming from the 2nd catch block  Based on what I ve read in Using TRY   CATCH in Transact-SQL  I think what s happening is that when the exception is thrown  the use of XACT ABORT is causing the transaction to be  terminated and rolled back     and then the first line of the BEGIN CATCH is blindly attempting to roll back again   I don t know why the original developer enabled XACT ABORT  so I m thinking the better solution  than removing it  would be to use XACT STATE   to only roll back if there is a transaction   lt  gt 0   Does that sound reasonable  Am I missing something   Also  the mention of logging in the error message makes me wonder  Is there another problem  potentially with configuration  Is our use of RAISEERROR   in this scenario contributing to the problem  Does that get logged  in some sort of case where logging isn t possible  as the error message alludes to

User · Answer

There are a few misunderstandings in the discussion above.

First, you can always ROLLBACK a transaction... no matter what the state of the transaction. So you only have to check the XACT_STATE before a COMMIT, not before a rollback.

As far as the error in the code, you will want to put the transaction inside the TRY. Then in your CATCH, the first thing you should do is the following:

 IF @@TRANCOUNT > 0
      ROLLBACK TRANSACTION @transaction

Then, after the statement above, then you can send an email or whatever is needed. (FYI: If you send the email BEFORE the rollback, then you will definitely get the "cannot... write to log file" error.)

This issue was from last year, so I hope you have resolved this by now :-) Remus pointed you in the right direction.

As a rule of thumb... the TRY will immediately jump to the CATCH when there is an error. Then, when you're in the CATCH, you can use the XACT_STATE to decide whether you can commit. But if you always want to ROLLBACK in the catch, then you don't need to check the state at all.

User · Answer

You always need to check for XACT STATE    irrelevant of the XACT ABORT setting  I have an example of a template for stored procedures that need to handle transactions in the TRY CATCH context at Exception handling and nested transactions   create procedure  usp my procedure name  as begin     set nocount on      declare  trancount int      set  trancount     trancount      begin try         if  trancount   0             begin transaction         else             save transaction usp my procedure name           -- Do the actual work here  lbexit          if  trancount   0                commit      end try     begin catch         declare  error int   message varchar 4000    xstate int          select  error   ERROR NUMBER                    message   ERROR MESSAGE                     xstate   XACT STATE            if  xstate   -1             rollback          if  xstate   1 and  trancount   0             rollback         if  xstate   1 and  trancount  gt  0             rollback transaction usp my procedure name           raiserror   usp my procedure name   d   s   16  1   error   message        end catch    end

User · Answer

Had the exact same error in a procedure  It turns out the user running it  a technical user in our case  did not have sufficient rigths to create a temporary table  EXEC sp addrolemember  db ddladmin    username here   did the trick

User · Answer

I have encountered this error while updating records from table which has trigger enabled  For example - I have trigger  Trigger1  on table  Table1   When I tried to update the  Table1  using the update query - it throws the same error  THis is because if you are updating more than 1 record in your query  then  Trigger1  will throw this error as it doesn t support updating multiple entries if it is enabled on same table  I tried disabling trigger before update and then performed update operation and it was completed without any error  DISABLE TRIGGER Trigger1 ON Table1  Update query -------- Enable TRIGGER Trigger1 ON Table1

[sql-server] SQL Transaction Error: The current transaction cannot be committed and cannot support operations that write to the log file

Examples related to sql-server

Examples related to sql-server-2005

Examples related to transactions