SQL Server Performance Forum – Threads Archive

Transaction Number

HI ALl I was wondering if anyone knows of a way to caputre the transaction number in SQL Server 2005. I found that there are functions to grab the state (xact_state) and count. But I am looking to get the actual transaction number. The reason I am looking for this because I am trying to implement activity logging on a new system using triggers. I want to associate what transaction my audit records are associated with.
Thanks

What do you mean by "transaction number"? —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

The transaction identifier. Or you could say the LSN number.

I might be wrong, but I don’t think you’ll get that value with a straight SQL query. One vague guess would be to use the undocumented DBCC LOG(<your db>, -1) command, try to pipe the into a table and try to figure out what that cryptic stuff actually means. If I understand you correctly, it should be much easier to record the table, the user, the date of change and anything else you need in your audit table. The LSN is a bad candidate for that kind of logging. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

You can capture TransactionID with Profiler. That would require you to capture the profiler output to a table if you want to be able to query it.

http://www.databasejournal.com/features/mssql/article.php/3587891
http://msdn2.microsoft.com/en-us/library/ms175976.aspx in addition what has been referred above. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

I found what I was looking for using the fn_dblog function.

I was just reading over the posts again and I am curious why the LSN would be a bad candidate for the logging.

This function is un documented and in 2005 also it is the same… How do you get the LSN info when your trigger fires?
You can pass NULL, NULL paremeter to the function and search for your trasaction based on "begin time", "end time" and SPID etc… http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-27-fn_dblog.htm
MohammedU.
Moderator
SQL-Server-Performance.com

SQL Server 2005 lets you use a log sequence number (LSN) to define the recovery point for a restore operation. This is a specialized feature that is intended for tools vendors and is unlikely to be generally useful. Also in a RECOVERY scenario the LSN plays important role in restoring to a nearest point of time, so in this case it is good and also bad when you are planning to implement such applications. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

]]>