sql | SQL Server‎ > ‎sqlsrv | T-SQL‎ > ‎

tsql | log status

--(from PASS TW FB group)

查交易紀錄檔目前狀態分享
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
Comments