Interesting Observation of DMV of Active Transactions and DMV of Curre

home
database
view story

8 Kicks

Interesting Observation of DMV of Active Transactions and DMV of Curre(blog.sqlauthority.com)

submitted by pinaldave(9662) 2 years, 10 months ago

This post is about a riveting observation I made a few days back. While playing with transactions I came across two DMVs that are associated with Transactions. 1) sys.dm_tran_active_transactions - Returns information about transactions for the instance of SQL Server. 2) sys.dm_tran_current_transaction - Returns a single row that displays the state information of the transaction in the current session. Now, what really interests me is the following observation.

1 comment |category: Database |Views: 107
Tweet

tags: SQLAuthority pinaldave SQLServer Database SQL another

Everyones tags:

Your Tags:

Please login or signup.

Add a live kick counter to your blog >> liveImage

You can even customize the image by choosing your own colors, and then clicking the button below to update the preview and the html code:

"Kick It" text
"Kick It" background
kick count text
kick count background
border

Simply copy and paste this HTML into your blog post.

Users who kicked this story:

jeet(0) -

JayP(0) -

mdopp(856) -

codebetter(0)

More who kicked this story...

Comments:

posted by JayP(0) 2 years, 9 months ago 0

I don't think I am following the logic behind your statements. When you run the first DMVs as separate transactions, the transaction IDs your are seeing in the result set reflect the transaction IDs of each independent transaction. Based on your comment as to what each DMV’s results represent, in the first table we see the tranID of the active transaction and in the second table we see the current transaction ID of the session.

When the second DMVs are run as an explicit transaction, we see what we expect. The current tranID and the active tranID are the same because the explicit in this instance is both the current and the active transaction.

I don’t understand why this would be a “riveting observation”. Isn’t this the manner we would expect our transactions to operate? Are you requesting a scenario in which we are specifically querying the Active and Current transaction information (transaction IDs) as implicit and explicit transactions? If that is the case, I don’t think there would be many useful scenarios, but I am not familiar with those DMVs.

Most Active Users

What's this?