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.

Errand

Please take a look at #3725856 – Mezuzah

Answer 1

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

See this article.

Wormy

Just what I was going to suggest. I have used this successfully many times. – Ruralize

The problem with this is if anything else happens like a connection timeout or execution timeout, you are going to be left with no logs. – Muley

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.

Protoxide

Both of those are good except I don't have easy access to the transaction part. Basically I'm trying to debug a program written in C#. – Errand

(let's finish this :-) The c# code starts a transaction, then calls a s-proc, and at the end it commits or rolls back the transaction. I only have easy access to the s-proc and I was hoping I could do some debugging code there that would survive a rollback – Errand

you could try using xp_cmdshell (Transact-SQL) (but that may be turned off as a security risk) to include these in your procedure: EXEC xp_cmdshell echo 'your message here' >>log.txt to write to a file. you could also try using an output parameter @ErrorInfo varchar(max)` where you pass back the error info to the C# program so it can log it. – Protoxide

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.

Coyne

Using CLR logging procedure is one of the best solutions. If only in was possible to somehow automatically get the actual values of parameters the sproc was called with..we can always dream. – Demodulation

Answer 4

Log output to a table, use a time delay, and use WITH(NOLOCK) to see it.

It looks like @arvid wanted to debug the operation of the stored procedure, and is able to alter the stored proc.

The c# code starts a transaction, then calls a s-proc, and at the end it commits or rolls back the transaction. I only have easy access to the s-proc

I had a similar situation. So I modified the stored procedure to log my desired output to a table. Then I put a time delay at the end of the stored procedure

WAITFOR DELAY '00:00:12';  -- 12 second delay, adjust as desired

and in another SSMS window, quickly read the table with READ UNCOMMITTED isolation level (the "WITH(NOLOCK)" below

SELECT * FROM dbo.NicksLogTable WITH(NOLOCK);

It's not the solution you want if you need a permanent record of the logs (edit: including where transactions get rolled back), but it suits my purpose to be able to debug the code in a temporary fashion, especially when linked servers, xp_cmdshell, and creating file tables are all disabled :-(

Apologies for bumping a 12-year old thread, but Microsoft deserves an equal caning for not implementing nested transactions or autonomous transactions in that time period.

Pendent

Answer 5

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.

Caracara

This answer doesn't answer the question at all. The OP is not asking about sub-transaction, quite the contrary. He wants some operation to be excluded of the transaction context and committed straightaway in the database. – Ker

my response was to the comment above it - and I think answered it quite succinctly – Caracara