--(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 |
sql | SQL Server > sqlsrv | T-SQL >