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

Question: I've suddeny started getting this error message in an ETL process via a SQL Server -  stored procedure. How can I troubleshoot ?  I'm looking for a way to report the errors just before this errror message - and get some clues to fix the issue.

Msg 3930, Level 16, State 1, Line 17
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

Answer: As a general approach this error occurs   when

a)  the TRY..CATCH BLOCK  is used . 

b) There is an issue with the transaction , e.g constraint error , foreing or primary key violation , data issue

c) attempting to commit .   This a good starting point to approach the error. 

Here is some code to recreate the problem.

It can be a challenge to identify the exact  root cause ,it can be for various reasons e.g lock conflict , low space , type mismatch  . TemppDB full , constraint violations, permission errors etc 

create temp table with Primatry as INT and add a row with value of 1
drop table if exists #myTable
create table #myTable(i int primary key) 
insert into #myTable values(1)
 
--When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
set XACT_ABORT  ON
 
-- use try and catch block
begin try
	begin transaction 
	insert into #myTable values(1)
	commit -- this commit will never happen, as we are violating the primary key constratint. 
end try
begin catch 
	print XACT_STATE() -- xact_state = (-1) suggests that the transaction is uncommitable.
	commit -- action acompleted part of the catch block, causing  error 3930 
end catch
 

The challenge is to monitor the stored procedure while it's being executed using the intended security context.   For example , you may execute the stored procedure and it works within the login id you use , but the ETL job may be executed by another  login id.  An effective method to record SQL Server Errors is Extended Events - read more on :  How to use Extended Events to report Query errors