How do you write to a table in a new transaction in T-SQL

Question

We have a requirement to log messages to a the database in our stored procedures - basically writing to a table. However if the transaction needs to be rolled back, we loose these logs.

Is there any way to write these logs (table inserts) in a new or different transaction to ensure they are persisted, regardless of the transaction of the current stored procedure?

Answer 1

Abuse the fact that Variables(including table) are exempt from transactions. Store logs in a table var, insert from it after doing your regular transaction.

This should be enough for your logging needs.

One obvious downside of this is that you won't be able to monitor messages in "real time", using (nolock), so consider inserting both into your LogTable and Table Variable and only use the TableVariable on Rollback