sys.dm_db_xtp_transactions – Day 65 – One DMV a Day

Hello Geeks and welcome to the Day 65 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.

Yesterday we have started with the In-Memory OLTP related DMVs. We have seen how to check the memory consumers in the Hekaton engine using sys.dm_db_xtp_memory_consumers. Today I will be covering sys.dm_db_xtp_transactions. This DMV gives the transaction details on an In-Memory table.

Sys.dm_db_xtp_transactions is different from sys.dm_tran_active_transactions. There can be natively compiled stored procedures which do not have an entry in the later DMV. All other transactions can be linked to a transaction id in the later DMV. Most of the columns returned are for internal use by Microsoft.

Sys.dm_db_xtp_transactions is useful when you are extensively using natively compiled stored procedures. Let us see the important columns from this DMV. In In-Memory OLTP there is no concept of locking and blocking. When a transaction is run it starts and proceed. When committing if the changes are no longer valid it fails. So few states of a transaction are no longer valid.

xtp_transaction_id – Id used for this transaction in the XTP transaction manager.

transaction_id – Can be used to link with sys.dm_tran_active_transactions. Value is 0 for XTP-only transactions.

session_id – Session which owns this transaction.

begin_tsn – Begin transaction sequence number of the transaction.

end_tsn – End transaction sequence number of the transaction.

state_desc – Status of the transaction. Values can be ACTIVE, COMMITTED, ABORTED, VALIDATING.


result_desc – Provides the outcome of the transaction. Possible values are IN PROGRESS, SUCCESS, ERROR, COMMIT DEPENDENCY, VALIDATION FAILED (RR), VALIDATION FAILED (SR), ROLLBACK

Let me run a simple select on the table we created and see the sample output from sys.dm_db_xtp_transactions.

SELECT xtp_transaction_id,
FROM sys.dm_db_xtp_transactions


Tomorrow I will be covering another In-Memory optimized tables related DMV. So stay tuned. Till then

Happy Learning,

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook


About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

Leave a Reply

Your email address will not be published.