title: SQL Transaction Error: The current transaction cannot be committed and cannot support operations that write to the log file
body: <p>I'm having a similar issue to <a href="https://stackoverflow.com/questions/2514284/the-current-transaction-cannot-be-committed-and-cannot-support-operations-that-wr"><em>The current transaction cannot be committed and cannot support operations that write to the log file</em></a>, but I have a follow-up question.</p>
  
  <p>The answer there references <a href="http://www.codeproject.com/KB/database/try_catch.aspx" rel="noreferrer"><em>Using TRY...CATCH in Transact-SQL</em></a>, which I'll come back to in a second...</p>
  
  <p>My code (inherited, of course) has the simplified form:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>This stored procedure is, intermittently, throwing errors like this one:</p>
  
  <blockquote>
    <p>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</p>
  </blockquote>
  
  <p>So obviously the error is coming from the 2nd catch block</p>
  
  <p>Based on what I've read in <a href="http://www.codeproject.com/KB/database/try_catch.aspx" rel="noreferrer"><em>Using TRY...CATCH in Transact-SQL</em></a>, I think what's happening is that when the exception is thrown, the use of <a href="http://msdn.microsoft.com/en-us/library/ms188792.aspx" rel="noreferrer"><code>XACT_ABORT</code></a> is causing the transaction to be "terminated and rolled back"... and then the first line of the <code>BEGIN CATCH</code> is blindly attempting to roll back again.</p>
  
  <p>I don't know why the original developer enabled <code>XACT_ABORT</code>, so I'm thinking the better solution (than removing it) would be to use <code>XACT_STATE()</code> to only roll back if there is a transaction (<code>&lt;&gt;0</code>). Does that sound reasonable? Am I missing something?</p>
  
  <p>Also, the mention of logging in the error message makes me wonder: Is there another problem, potentially with configuration? Is our use of <code>RAISEERROR()</code> 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?</p>