For auditing purposes, I need to get the unique transaction id from inside the current transaction. I will log the mapping between this transaction id and the application specific session id in a table. Then when looking at the transaction logs, I can find out which application session is responsible for a change in a row.
But it seems that transaction id is not unique for the database and is reset to 0 after a server restart. Querying sys.dm_tran_current_transaction proves so.
So, how can I get such a unique id for a transaction in the transaction logs? Or should I use SQL server audit? Is there a way to query the audit id from inside the transaction?
Best Answer
Stop trying to use the transaction log for these types of situations. Take any notion you have of using the log for data mining and defenestrate it.
Judging by your other questions, I don't think SQL Audit would work for you. Most likely you're going to need to change your application and build in auditing to gather what you're looking for due to the complex requirements you are looking for.
The application could be changed to generate its' own transaction id with the id of the web user, etc., and log that to an auditing area. That's, however, up to the application but would get you want you want. Attempting to continue to mine transaction log data will at best cause you throughput issues and at worse crash/suspect your database.