June 12, 2012 at 9:58 pm
If I restart my local SQL Server instance and run:
SELECT DB_NAME(database_id), * FROM sys.dm_tran_database_transactions
I see the following (first three columns shown):
tempdb1422
tempdb1542
tempdb1492
tempdb1472
tempdb1452
distribution22287
tempdb1522
master22281
NULL222832767
BOL says the system view "Returns information about transactions at the database level." But besides that, the information I've been able to find is very limited.
What do these rows mean? And how does this relate to the other system view sys.dm_tran_session_transactions?
June 15, 2012 at 8:11 am
Yes, you can join it to sys.dm_tran_session_transactions on transaction_id and from there you have lots of option for joining sys.dm_tran_session_transactions to other DMVs via the session_id column. Once you join to a DMV like sys.dm_exec_sessions or sys.dm_exec_requests, you are able to get stuff like the query plans and SQL text by joining to other DMOs like sys.dm_exec_sqltext.
In the sample query below, I am using Left Joins because much of the info does not have an active session associated with it. Start a transaction in a database to see some sample data with session information.
SELECT DB_NAME(DT.database_id) As DBName,
TranType = Case DT.database_transaction_type
When 1 Then 'Read/write transaction'
When 2 Then 'Read-only transaction'
When 3 Then 'System transaction'
End,
TranState = Case database_transaction_state
When 1 Then 'Transaction has not been initialized'
When 3 Then 'Transaction has been initialized but has not generated any log records'
When 4 Then 'Transaction has generated log records'
When 5 Then 'Transaction has been prepared'
When 10 Then 'Transaction has been committed'
When 11 Then 'Transaction has been rolled back'
When 12 Then 'Transaction is being committed. In this state the log record is being generated, but it has not been materialized or persisted'
End,
*
FROM sys.dm_tran_database_transactions DT
Left Join sys.dm_tran_session_transactions ST ON ST.transaction_id = DT.transaction_id
Left Join sys.dm_exec_sessions S On S.session_id = ST.session_id
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply