DMV-11: T-Log space used by Transaction……..sys.dm_tran_database_transactions

Introduction

sys.dm_tran_database_transactions DMV (Dynamic Management View) has been described by BOL as follows:

• http://msdn.microsoft.com/en-us/library/ms186957.aspx

Returns information about transactions at the database level.

Some time , T-log space is hugely consumed by database & we are not aware which transaction is the culprit for this. This is useful DMV in such conditions to check T-log space used by each transaction on database.

Query 1 : Query to check Transaction log space used by each transaction will complete detail

SELECT
ST.SESSION_ID,
DT.TRANSACTION_ID,
DB_NAME(DT.DATABASE_ID) DB_NAME,
DATABASE_TRANSACTION_BEGIN_TIME TRANSACTION_BEGIN_TIME,
CASE DATABASE_TRANSACTION_TYPE
WHEN 1 THEN ‘READ/WRITE’
WHEN 2 THEN ‘READ ONLY’
WHEN 3 THEN ‘SYSTEM’ END AS TRANSACTION_TYPE,
CASE DATABASE_TRANSACTION_STATE
WHEN 1 THEN ‘NOT INITIALIZED’
WHEN 3 THEN ‘TRANSACTION NO LOG’
WHEN 4 THEN ‘TRANSACTION WITH LOG’
WHEN 5 THEN ‘TRANSACTION PREPARED’
WHEN 10 THEN ‘COMMITED’
WHEN 11 THEN ‘ROLLED BACK’
WHEN 12 THEN ‘COMMITED AND LOG GENERATED’ END AS TRANSACTION_STATE,
SP.HOSTNAME,
SP.LOGINAME,
SP.STATUS,
SP.LASTWAITTYPE,
SQLT.TEXT,
DATABASE_TRANSACTION_LOG_RECORD_COUNT LOG_RECORD_COUNT,
(DATABASE_TRANSACTION_LOG_BYTES_USED + _
DATABASE_TRANSACTION_LOG_BYTES_RESERVED )/1024 TOTAL_LOG_SPACE_USED_KB,
DATABASE_TRANSACTION_LOG_BYTES_USED LOG_BYTES_USED,
DATABASE_TRANSACTION_LOG_BYTES_RESERVED LOG_BYTES_RESERVED
FROM
SYS.DM_TRAN_DATABASE_TRANSACTIONS DT JOIN
SYS.DM_TRAN_SESSION_TRANSACTIONS ST
ON DT.TRANSACTION_ID=ST.TRANSACTION_ID
JOIN
SYS.SYSPROCESSES SP
ON SP.SPID = ST.SESSION_ID
CROSS APPLY
SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) SQLT
WHERE
DT.TRANSACTION_ID > 1000 AND ST.SESSION_ID >50

Sample Result

Remarks

1. Meaning of int value for database_transaction_type column
• 1 = Read/write transaction
• 2 = Read-only transaction
• 3 = System transaction
2. All possible state of transactions for database_transaction_state column
• 1 = The transaction has not been initialized.
• 3 = The transaction has been initialized but has not generated any log records.
• 4 = The transaction has generated log records.
• 5 = The transaction has been prepared.
• 10 = The transaction has been committed.
• 11 = The transaction has been rolled back.
• 12 = The transaction is being committed. In this state, the log record is being generated, but it has not been materialized or persisted.
3. To use this DMV, User required VIEW SERVER STATE permission on the server.
4. If column database_transaction_begin_time has NULL, then transaction is read only otherwise read write.
5. TRANSACTION_ID > 1000 & SESSION_ID > 50 is used for user transactions & sessions.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference: Rohit Garg (http://mssqlfun.com/)