현재 Transaction Log를 사용중인쿼리 확인

-- Purpose: Report active transactions by space or duration.-- Author: I. Stirk.

-- Do not lock anything, and do not get held up by any locks.SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- What SQL statements are currently using the transaction logs?SELECT tst.session_id

  , es.original_login_name

  , DB_NAME(tdt.database_id) AS DatabaseName

  , DATEDIFF(SECOND, tat.transaction_begin_time, GETDATE()) AS [TransDuration(s)]

  , tdt.database_transaction_log_record_count AS SpaceUsed

  , CASE tat.transaction_state

      WHEN 0 THEN 'The transaction has not been completely initialized yet'

      WHEN 1 THEN 'The transaction has been initialized but has not started'

      WHEN 2 THEN 'The transaction is active'

      WHEN 3 THEN 'The transaction has ended'

      WHEN 4 THEN 'The commit process has been initiated on the distributed tran'

      WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution'

      WHEN 6 THEN 'The transaction has been committed'

      WHEN 7 THEN 'The transaction is being rolled back'

      WHEN 8 THEN 'The transaction has been rolled back'

      ELSE 'Unknown'

  END AS TransactionState

  , SUBSTRING(TXT.text, ( er.statement_start_offset / 2 ) + 1,

       ( ( CASE WHEN er.statement_end_offset = -1

                     THEN LEN(CONVERT(NVARCHAR(MAX), TXT.text)) * 2

                     ELSE er.statement_end_offset

              END - er.statement_start_offset ) / 2 ) + 1) AS CurrentQuery

  , TXT.text AS ParentQuery

  , es.host_name

  , CASE tat.transaction_type

      WHEN 1 THEN 'Read/Write Transaction'

      WHEN 2 THEN 'Read-Only Transaction'

      WHEN 3 THEN 'System Transaction'

              WHEN 4 THEN 'Distributed Transaction'

              ELSE 'Unknown'

  END AS TransactionType

  , tat.transaction_begin_time AS StartTime

FROM sys.dm_tran_session_transactions AS tst

       INNER JOIN sys.dm_tran_active_transactions AS tat

              ON tst.transaction_id = tat.transaction_id

       INNER JOIN sys.dm_tran_database_transactions AS tdt

              ON tst.transaction_id = tdt.transaction_id

       INNER JOIN sys.dm_exec_sessions es

              ON tst.session_id = es.session_id

       INNER JOIN sys.dm_exec_requests er

              ON tst.session_id = er.session_id

       CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) TXT

--ORDER BY tdt.database_transaction_log_record_count DESC -- log space size.ORDER BY [TransDuration(s)] DESC -- transaction duration.