sys.dm_tran_current_transaction – Day 51 – One DMV a Day

Hello Geeks and welcome to the Day 51 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.

After the significant 50 blogs in the DMV series and a week of PASS Summit in my head, I am in cloud 9. I still can’t believe that I have completed 50 blogs in the series. The day I started I was not sure if I could reach the first milestone of 31 blogs of any one day series on SQL Server. But look at this post today, 51 days. You just need the commitment to do what you are doing consistently.

Having said that let me jump to today’s dose of DMV. To keep it light after the ultramarathon, I will be covering sys.dm_tran_current_transaction. This DMV is a limited result set very specific to the current session. This helps in analyzing the state of few parameters when you are inside a transaction.

Let us learn the output of sys.dm_tran_current_transaction with the below example. I will open a transaction which will run an update and create few worktables and a temp table.

BEGIN TRANSACTION

UPDATE Person.Person
	 SET Title = Title
WHERE BusinessEntityID < 10000

SELECT transaction_id AS [tranId],
	 transaction_sequence_num AS [seqNo],
	 transaction_is_snapshot AS [isSnap],
	 first_snapshot_sequence_num AS [1stSnapSeqNo],
	 last_transaction_sequence_num AS [lastSeqNo],
	 first_useful_sequence_num AS [1stUsefulSeqNo]
FROM sys.dm_tran_current_transaction

IF (OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL)
	 DROP TABLE #temp

SELECT * 
INTO #temp
FROM bigTransactionHistory
ORDER BY ActualCost

--COMMIT

1 sys.dm_tran_current_transaction

Now in a new session run the select on sys.dm_tran_current_transaction.

SELECT transaction_id AS [tranId],
	 transaction_sequence_num AS [seqNo],
	 transaction_is_snapshot AS [isSnap],
	 first_snapshot_sequence_num AS [1stSnapSeqNo],
	 last_transaction_sequence_num AS [lastSeqNo],
	 first_useful_sequence_num AS [1stUsefulSeqNo]
FROM sys.dm_tran_current_transaction

2 sys.dm_tran_current_transaction

The output displays the transaction_id which is unique for the lifetime of a running instance. It gets reset after a restart. Now the interesting columns are the sequence numbers. The transaction_sequence_number is the sequence number of current transaction. The next column, transaction_is_snapshot is to tell if this is a snapshot transaction.

First_snapshot_sequence_num is the lowest sequence number of any transaction which is active when first snapshot is generated. Last_transaction_sequence_num is the last sequence number generated. First_useful_sequence_num is the oldest sequence number that should be retained in version store. I will cover more about version store in the next two DMVs. For now we can conclude from this column that sequence number lower than this can be removed.

In the second output you will observe the first_useful_sequence_num is 1358 which is the sequence number for the first session. Now commit the first session and run the second command again.

3 sys.dm_tran_current_transaction

 

You will see the value change to 1362. It will be any number greater than 1358. In this case it is 1362. This means any sequence number before 1362 can be removed.

Now you know the significance of sys.dm_tran_current_transaction. Keep guessing what I will be covering tomorrow. So stay tuned. Till then.

Happy Learning,
Manu

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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

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. Required fields are marked *