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

Two of my colleagues had the following error recently and I helped them resolve it. So I thought of writing a blog on how to prevent this error with a simple example.

The error is :

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

The following code will throw this exception:



--drop table #temp
--drop table error

create table #temp(val int primary key)
create table error(error nvarchar(2000))

set xact_abort on
begin try
begin tran
insert #temp(val) values(1)
insert #temp(val) values(2)
insert #temp(val) values(1)    -- duplicate key
insert #temp(val) values(3)
commit
end try
begin catch
declare @error nvarchar(2000)
set @error = error_message()
insert error(error)
select @error

if @@trancount > 0
rollback
end catch


The exception is thrown because it is trying to write to a table within the catch block before rolling back the transaction. The solution is to move the insert statement after the rollback. And it is really important to log error messages after the rollback bacause the logged messages also will be rolled back with the subsequent rollback tran statement. The following code will not throw the exception:



--drop table #temp
--drop table error

create table #temp(val int primary key)
create table error(error nvarchar(2000))

set xact_abort on
begin try
begin tran
insert #temp(val) values(1)
insert #temp(val) values(2)
insert #temp(val) values(1) -- duplicate key
insert #temp(val) values(3)
commit
end try
begin catch
declare @error nvarchar(2000)
set @error = error_message()
if @@trancount > 0
rollback
insert error(error)
select @error

end catch

Advertisements