TSQL logging inside transaction

Question

I'm trying to write to a log file inside a transaction so that the log survives even if the transaction is rolled back.

--start code

begin tran

insert [something] into dbo.logtable

[[main code here]]

rollback

commit

-- end code

You could say just do the log before the transaction starts but that is not as easy because the transaction starts before this S-Proc is run (i.e. the code is part of a bigger transaction)

So, in short, is there a way to write a special statement inside a transaction that is not part of the transaction. I hope my question makes sense.

Answer 1

Use a table variable (@temp) to hold the log info. Table variables survive a transaction rollback.

See this article.

Answer 2

I do this one of two ways, depending on my needs at the time. Both involve using a variable, which retain their value following a rollback.

1) Create a DECLARE @Log varchar(max) value and use this: @SET @Log=ISNULL(@Log+'; ','')+'Your new log info here'. Keep appending to this as you go through the transaction. I'll insert this into the log after the commit or the rollback as necessary. I'll usually only insert the @Log value into the real log table when there is an error (in theCATCH` block) or If I'm trying to debug a problem.

2) create a DECLARE @LogTable table (RowID int identity(1,1) primary key, RowValue varchar(5000). I insert into this as you progress through your transaction. I like using the OUTPUT clause to insert the actual IDs (and other columns with messages, like 'DELETE item 1234') of rows used in the transaction into this table with. I will insert this table into the actual log table after the commit or the rollback as necessary.

Answer 3

If the parent transaction rolls back the logging data will roll back as well - SQL server does not support proper nested transactions. One possibility is to use a CLR stored procedure to do the logging. This can open its own connection to the database outside the transaction and enter and commit the log data.

Answer 4

If you want to emulate nested transaction behaviour you can use named transactions:

begin transaction a

create table #a (i  int)

select * from #a
save transaction b

create table #b (i  int)
select * from #a
select * from #b

rollback transaction b

select * from #a
rollback transaction a

In SQL Server if you want a ‘sub-transaction’ you should use save transaction xxxx which works like an oracle checkpoint.