세션 ID별 트랜젝션 로그 사용율

세션 ID별 트랜젝션 로그 사용율 확인

SELECT es.session_id,
	es.host_name,
	es.host_process_id,
	es.login_name,
	CASE tdt.[database_transaction_state] 
	 WHEN 1 THEN 'The transaction has not been initialized.' 
	 WHEN 3 THEN 'The transaction has been initialized.
	 	 but has not generated any log records.' 
	 WHEN 4 THEN 'The transaction has generated log records.' 
	 WHEN 5 THEN 'The transaction has been prepared.' 
	 WHEN 10 THEN 'The transaction has been committed.' 
	 WHEN 11 THEN 'The transaction has been rolled back.' 
	 WHEN 12 THEN 'The transaction is being committed.
	 	 In this state the log record is being generated,
	 	 but it has not been materialized or persisted.' 
	 ELSE NULL 
	 END [DatabaseTransactionStateDesc] 
	,tdt.database_transaction_begin_time
	,tdt.database_transaction_begin_lsn
	,convert(decimal(15,3),
	 	 (tdt.database_transaction_log_bytes_used/1024.0/1024.0))
	 	 AS database_transaction_log_Mbytes_used
	,tdt.database_transaction_log_record_count
FROM sys.dm_exec_sessions es 
INNER JOIN sys.dm_tran_session_transactions tst 
	 ON es.[session_id] = tst.[session_id] 
INNER JOIN sys.dm_tran_database_transactions tdt 
	 ON tst.[transaction_id] = tdt.[transaction_id] 
WHERE tdt.[database_transaction_state] >= 4 
ORDER BY database_transaction_begin_time ASC