How to get the MSSQL Transaction_ID

[--Brad--]

I am trying to tie the Qlik Replicate change table to data in SQL Audit on MS SQL Server.  I thought I would be able to do this with the transaction_id in Qlik Replicate, but that transaction_id does not match the transaction_id generated by MS SQL.

Qlik's is alphanumeric where SQL's is numeric only.  I read in the Qlik Replicate documentation that The actual value is a hex-string of the 128-bit transaction ID." but when I decode Qlik's transaction_id, it still is not similar to what is in SQL:

Are these totally different transaction_ids?  Am I just decoding it incorrectly?  Can I get the SQL transaction_id some other way? 

Thanks!
Brad

[john_wang · Accepted solution]

Hello @--Brad-- ,

This is really a good question. Per my researching:

1. I'm afraid there is not an explicit mapping between transaction_id (in sys.dm_tran_database_transactions or other similar views) and [Transaction ID] (in TLOG, or say in fn_dblog() ). You cannot convert from one to another by a formula or vice versa because they are totally different things.

    However we can find their relationship, I will describe in detailed below.

2. In Replicate, we use  [Transaction ID] which gets from TLOG.

    This is what you see in column "header__transaction_id".

3. Personally I do not think you can use transaction_id  for your SQL Audit purpose, at least it's hard to use because it restarts at 0 after a server restart.

How to find out header__transaction_id by transaction_id 

• Get transaction_id from active transactions:

select * from sys.dm_tran_database_transactions WHERE  database_id = DB_ID();

        In my sample, the transaction_id is 18129.

• Get the [Transaction ID] from TLOG by using transaction_id 18129

SELECT [Transaction ID]
FROM   sys.fn_dblog(NULL, NULL)
WHERE  [Xact ID] = 18129;

        In my sample the [Transaction ID] is '0000:000016e6'

• Get other information from TLOG by using [Transaction ID]

SELECT *                                  
FROM   sys.fn_dblog(NULL, NULL)           
WHERE  [Transaction ID] = '0000:000016e6';

        In Replicate target table the header__transaction_id looks like: E6160000000000000000000000000000

Hope this helps.

Regards,

John.

[--Brad--]

Thanks @john_wang ,

I see the relationship that you are talking about  and have been able to trace the lineage from Replicate's header__transaction_id to the transaction_id that's in SQL Audit.  I am working through how I would do this in a production scenario and my options don't seem ideal so far.

I am wondering:  Is there any way to get Qlik Replicate to capture the XACT_ID (ie: transaction_id from SQL Audit) that is in the LDF file at the same time it is importing the rest of the data changes?

Thanks,
Brad

 

 

[john_wang]

Hello @--Brad-- ,

I was in public holiday in the past week. Sorry for the delay.

I'm afraid we are unable to get XACT_ID from TLOG in current Replicate versions yet. you may find all the header information by enable the store changes table. Please raise Feature Request in Suggest an Idea area if you really need it.

Regards,

John.

[john_wang]

Hello @--Brad-- ,

Noticed you opened an idea article Capture XACT ID transaction ID during MS SQL Replication . Let's wait for PM team update.

Thank you,

John.

[--Brad--]

Thank you, @john_wang.