Transaction ID

Question

Hello,

How can I give a transaction an ID in order to find it later or to carry out a query?

Tank you

Answer 1

CURRENT_TRANSACTION_ID

Answer 2

Hi @Alice ,

You could use CURRENT_TRANSACTION_ID inside one transaction to catch the current transaction ID.

Please refer below two queries:

  SELECT CURRENT_TRANSACTION_ID();   

  SELECT CONVERT(VARCHAR, CURRENT_TRANSACTION_ID())  

Then we could query more details in DMV sys.dm_tran_database_transactions or use fn_dblog to report all information about this transaction by searching the transaction ID we catched above.

USE DATABASENAME -- Specify Database Name  
GO  
SELECT   
    [Transaction Name],  
    [Description],  
    [Operation],  
    [Transaction ID],  
    [Transaction SID],  
    [Begin Time],  
    [End Time]  
FROM fn_dblog(NULL, NULL)  
where [Xact ID]=transactionid--Specify the transactionid  

OR

SELECT *  
FROM   sys.dm_tran_database_transactions  
WHERE  transaction_id=transactionid--Specify the transactionid  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

I highly recommend you use your own transaction id (via a sequence or identity field) and pass it. This will give you much more control over the assignment and grouping of multiple SQL transactions into a single "logical" transaction.