查詢某個交易所耗用 Transaction Log 的量

–取自 Performance Tuning with SQL Server Dynamic Management Views  P.147

select DTST.Session_id,DES.login_name [登入帳號],DB_NAME(DTDT.database_id) [資料庫],
DTDT.database_transaction_begin_time AS [起始時間],DATEDIFF(ms,DTDT.database_transaction_begin_time,GETDATE()) AS [耗時(ms)],
CASE DTAT.transaction_type
WHEN 1 THEN ‘read/write’
WHEN 2 THEN ‘read-only’
WHEN 3 THEN ‘System’
WHEN 4 THEN ‘Distributed’ END [交易類型],
CASE DTAT.transaction_state
WHEN 0 THEN ‘未完全初始化’
WHEN 1 THEN ‘已初始化,未開始’
WHEN 2 THEN ‘啟動’
WHEN 3 THEN ‘結束’
WHEN 4 THEN ‘Commit initiated’
WHEN 5 THEN ‘Prepared, awaiting resolution’
WHEN 6 THEN ‘Committed’
WHEN 7 THEN ‘Rolling back’
WHEN 8 THEN ‘Rolled back’ END [交易狀態],
DTDT.database_transaction_log_record_count [交易紀錄(log)筆數],
DTDT.database_transaction_log_bytes_used [交易紀錄 byte 數],
DTDT.database_transaction_log_bytes_reserved [交易紀錄保留 byte 數],
DEST.text [最後交易文字],
DEQP.query_plan [最後查詢計畫]
from sys.dm_tran_database_transactions DTDT
JOIN sys.dm_tran_session_transactions DTST ON DTST.transaction_id=DTDT.transaction_id
JOIN sys.dm_tran_active_transactions DTAT ON DTST.transaction_id=DTAT.transaction_id
JOIN sys.dm_exec_sessions DES ON DES.session_id=DTST.session_id
JOIN sys.dm_exec_connections DEC ON DEC.session_id=DTST.session_id
LEFT JOIN sys.dm_exec_requests DER ON DER.session_id=DTST.session_id
CROSS APPLY sys.dm_exec_sql_text(DEC.most_recent_sql_handle) AS DEST
OUTER APPLY sys.dm_exec_query_plan(DER.plan_handle) AS DEQP
ORDER BY DTDT.database_transaction_log_bytes_used DESC

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: