---

USE master;
SET NOCOUNT ON;

-- 1. Активные запросы в наших БД и соединения репликации.
SELECT
    GETDATE() AS checkedAt,
    r.session_id,
    s.program_name,
    s.login_name,
    s.host_name,
    DB_NAME(r.database_id) AS databaseName,
    r.status,
    r.command,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time AS waitMs,
    r.wait_resource,
    r.total_elapsed_time AS elapsedMs,
    r.cpu_time AS cpuMs,
    r.reads,
    r.logical_reads,
    r.writes,
    r.open_transaction_count,
    SUBSTRING(
        txt.text,
        r.statement_start_offset / 2 + 1,
        (
            CASE
                WHEN r.statement_end_offset = -1
                    THEN DATALENGTH(txt.text)
                ELSE r.statement_end_offset
            END - r.statement_start_offset
        ) / 2 + 1
    ) AS currentStatement,
    txt.text AS batchText
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE r.session_id <> @@SPID
  AND
  (
      r.database_id IN
      (
          DB_ID(N'myNewVasya'),
          DB_ID(N'replTransVas'),
          DB_ID(N'distribution')
      )
      OR s.program_name LIKE N'%Distribution%'
      OR s.program_name LIKE N'%LogReader%'
      OR s.program_name LIKE N'%Replication%'
  )
ORDER BY
    CASE WHEN r.blocking_session_id > 0 THEN 0 ELSE 1 END,
    r.total_elapsed_time DESC;

-- 2. Кто блокирует запросы.
-- Показывает также спящие соединения с открытой транзакцией.
SELECT
    blocked.session_id AS blockedSession,
    DB_NAME(blocked.database_id) AS blockedDatabase,
    blocked.wait_type,
    blocked.wait_time AS waitMs,
    blocked.wait_resource,
    blocked.blocking_session_id AS blockerSession,
    blocker.status AS blockerStatus,
    blocker.login_name AS blockerLogin,
    blocker.host_name AS blockerHost,
    blocker.program_name AS blockerProgram,
    blocker.open_transaction_count AS blockerOpenTransactions,
    blocker.last_request_start_time,
    blocker.last_request_end_time,
    blockerSql.text AS blockerCurrentOrLastBatch
FROM sys.dm_exec_requests AS blocked
LEFT JOIN sys.dm_exec_sessions AS blocker
    ON blocker.session_id = blocked.blocking_session_id
LEFT JOIN sys.dm_exec_requests AS blockerRequest
    ON blockerRequest.session_id = blocked.blocking_session_id
LEFT JOIN sys.dm_exec_connections AS blockerConnection
    ON blockerConnection.session_id = blocked.blocking_session_id
OUTER APPLY sys.dm_exec_sql_text(
    COALESCE(
        blockerRequest.sql_handle,
        blockerConnection.most_recent_sql_handle
    )
) AS blockerSql
WHERE blocked.blocking_session_id > 0
  AND blocked.session_id <> @@SPID
ORDER BY blocked.wait_time DESC;

-- 3. Открытые транзакции в реплике.
SELECT
    st.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status,
    at.transaction_begin_time,
    at.transaction_type,
    at.transaction_state,
    dt.database_transaction_log_bytes_used,
    dt.database_transaction_log_bytes_reserved
FROM sys.dm_tran_database_transactions AS dt
JOIN sys.dm_tran_active_transactions AS at
    ON at.transaction_id = dt.transaction_id
LEFT JOIN sys.dm_tran_session_transactions AS st
    ON st.transaction_id = dt.transaction_id
LEFT JOIN sys.dm_exec_sessions AS s
    ON s.session_id = st.session_id
WHERE dt.database_id = DB_ID(N'replTransVas')
ORDER BY at.transaction_begin_time;