This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(70.4, 13%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ES%3C/text%3E%3C/svg%3E)
Hi All,
Which DMV will tell me the transaction mode of a SPID?
I did check MSDN but couldn't get the answer.
https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-session-transactions-transact-sql?view=sql-server-ver16
Regards,
Sam
You can join sys.dm_tran_session_transactions and sys.dm_tran_active_transaction.
This will give you information whether the transaction is a distributed transaction. I don't think that there is anyway to see the distinction between the first four. (And engine-wise there is not much difference between them.)
okay. Thank you.
I was wrong. There is a way. (And I should know this, as I have this discussion in one of my talks, albeit I talk about deadlock graphs, not DMVs).
In sys.dm_tran_active_transaction. there is the name column. It gives you information you need.
If it says user_transaction, it is a transaction explicitly started with BEGIN TRANSACTION. The same is true if it reads MyTran, Bridget or some other name. In this case, the code says BEGIN TRANSACTION MyTran, that is an explicit name was given.
If it says implicit_transaction, it is a transaction that was started implicitly by a SELECT, INSERT or other statement because SET IMPLICIT_TRANSACTIONS ON is in force. (A very rarely used setting in SQL Server, but it is actually the ANSI standard.)
If it says SELECT, INSERT, DELETE etc, it is most likely a an autocomitted statement. (But someone might be pulling your legs by saying BEGIN TRANSACTION "INSERT".) Beware, though, that the transaction may still encompass multiple statements if there is a trigger, or the name is INSERT EXEC.
You can identify a distributed transaction by the column transaction_type being 4.
Finally in your list, there is batch-scoped transaction. I am not exactly sure what you have in mind here. What would the difference be comapared to a regular multi-statement transaction?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Hi @Samantha r
I looked through all the relevant DMVs and found only two columns related to transaction type.
The sys.dm_tran_active_transactions returns a column named transaction_type that contains four transaction types: read/write transactions, read-only transactions, system transactions, and distributed transactions.
The sys.dm_tran_database_transactions returns a column named database_transaction_type, which contains read/write transactions, read-only transactions, and system transactions.
Like Erland said above, there seems to be no way to see the difference between the first four.
Best regards,
Percy Tang
If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".
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.