Просмотр текущих транзакций MS SQL

Материал из Course Orchestra
Перейти к: навигация, поиск

Описание

Данный запрос возвращает список активных транзакций, время их работы и в поле session_id - id из sp_who, так что можно отменить процесс командой kill.

SELECT sys.dm_tran_active_transactions.transaction_id,
    sys.dm_tran_active_transactions.name,
    sys.dm_tran_active_transactions.transaction_begin_time,
    DATEDIFF(SECOND,sys.dm_tran_active_transactions.transaction_begin_time,GETDATE()) AS Duration,
    sys.dm_tran_active_transactions.transaction_type,
    sys.dm_tran_session_transactions.session_id,
    sys.dm_tran_session_transactions.is_local,
    sys.dm_exec_sessions.host_name,
    sys.dm_exec_sessions.login_name
FROM sys.dm_tran_active_transactions 
    INNER JOIN sys.dm_tran_session_transactions 
        ON sys.dm_tran_active_transactions.transaction_id=sys.dm_tran_session_transactions.transaction_id
    INNER JOIN sys.dm_exec_sessions 
        ON sys.dm_tran_session_transactions.session_id=sys.dm_exec_sessions.session_id


Этим запросом вы можете посмотреть непосредственно процедуру и кусок кода который выполняется.

SELECT 
   r.[session_id],
   c.[client_net_address],
   s.[host_name],
   c.[connect_time],
   [request_start_time] = s.[last_request_start_time],
   [current_time] = CURRENT_TIMESTAMP,
   r.[percent_complete],
   [estimated_finish_time] = DATEADD
       (
           MILLISECOND,
           r.[estimated_completion_time], 
           CURRENT_TIMESTAMP
       ),
   current_command = SUBSTRING
       (
           t.[text],
           r.[statement_start_offset]/2,
           COALESCE(NULLIF(r.[statement_end_offset], -1)/2, 2147483647)
       ),
   module = COALESCE(QUOTENAME(OBJECT_SCHEMA_NAME(t.[objectid], t.[dbid])) 
       + '.' + QUOTENAME(OBJECT_NAME(t.[objectid], t.[dbid])), '<ad hoc>'),
   [status] = UPPER(s.[status])
 FROM
     sys.dm_exec_connections AS c
 INNER JOIN
     sys.dm_exec_sessions AS s
     ON c.session_id = s.session_id
 LEFT OUTER JOIN
     sys.dm_exec_requests AS r
     ON r.[session_id] = s.[session_id]
 OUTER APPLY
     sys.dm_exec_sql_text(r.[sql_handle]) AS t
 WHERE
     c.session_id = 128;


Удобный запрос, в котором показывается, длительность транзакции, а так же конкретный кусок кода T-SQL который выполняется.

SELECT sys.dm_tran_active_transactions.transaction_id,
    sys.dm_tran_active_transactions.name,
    sys.dm_tran_active_transactions.transaction_begin_time,
    DATEDIFF(SECOND,sys.dm_tran_active_transactions.transaction_begin_time,GETDATE()) AS Duration,
    sys.dm_tran_active_transactions.transaction_type,
    sys.dm_tran_session_transactions.session_id,
    sys.dm_tran_session_transactions.is_local,
    sys.dm_exec_sessions.host_name,
    sys.dm_exec_sessions.login_name,
    current_command = SUBSTRING
       (
           t.[text],
           r.[statement_start_offset]/2,
           COALESCE(NULLIF(r.[statement_end_offset], -1)/2, 2147483647)
       ),
     module = COALESCE(QUOTENAME(OBJECT_SCHEMA_NAME(t.[objectid], t.[dbid])) 
       + '.' + QUOTENAME(OBJECT_NAME(t.[objectid], t.[dbid])), '<ad hoc>')
FROM sys.dm_tran_active_transactions 
    INNER JOIN sys.dm_tran_session_transactions 
        ON sys.dm_tran_active_transactions.transaction_id=sys.dm_tran_session_transactions.transaction_id
    INNER JOIN sys.dm_exec_sessions 
        ON sys.dm_tran_session_transactions.session_id=sys.dm_exec_sessions.session_id
    left outer join sys.dm_exec_requests AS r
		ON r.[session_id] = sys.dm_exec_sessions.[session_id]
	outer apply sys.dm_exec_sql_text(r.[sql_handle]) AS t

{{#allow-groups:user}}