Getting Transaction ID

Question

Getting Transaction ID

felipe-462297

SSC Enthusiast

Points: 146
More actions
February 11, 2008 at 12:25 pm

#74039

How can I get the Transaction ID inside "BEGIN...COMMIT" commands?
Thank you.
Felipe Melo

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

WHAT do you mean by "Transaction ID"? Do you mean the level of nesting within the transaction or do you mean the IDENTITY number of a row you just inserted into a table that has an IDENTITY column?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

felipe-462297 SSC Enthusiast Points: 146 More actions · Answer 2

Hi Jeff,

Please corrrect if I'm making any mistake, but I think SQL creates an unique ID to each opened transaction.

If it exist, I need to get this ID number.

Thank you for your reply.

Nic Hopper-586355 Ten Centuries Points: 1161 More actions · Answer 3

Thats an interesting question, as far as i was aware transaction where managed at the session id, db and object id level.

All this information should be in syslocks, or you could return the session id from @@Spid.

Would interested to know if there is a unique transaction id or guid that is visible and not just internal to SQL.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

felipe (2/12/2008)
Hi Jeff,
Please corrrect if I'm making any mistake, but I think SQL creates an unique ID to each opened transaction.
If it exist, I need to get this ID number.
Thank you for your reply.

That may be... but that type of thing would go to the transaction log and that particular "transaction ID" doesn't have a public facing interface function that I know of (in plain English... I don't know how to get that number using SQL 😉 ).

The only number that I know you can get automatically is if the table has an IDENTITY column. Then you can use the SCOPE_IDENTITY() function to return the ID of the row you last inserted... transaction or not.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

andrew.whettam SSC Enthusiast Points: 194 More actions · Answer 5

I know it's a bit late (the thread was opened in 2008!), but here is the answer:

SELECT transaction_id FROM sys.dm_tran_current_transaction;

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

andrew.whettam (9/17/2013)
I know it's a bit late (the thread was opened in 2008!), but here is the answer:
SELECT transaction_id FROM sys.dm_tran_current_transaction;

This is in the sql 2000 forum. That dmv was not introduced until sql 2005.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/