---

USE [replTransVas];
SET NOCOUNT ON;

-- 1. Фактическое состояние индексов после ошибки 1222.
SELECT
    N'01_INDEXES' AS section,
    name,
    type_desc,
    is_disabled
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.sb_answer')
ORDER BY index_id;

-- 2. Два снимка работы сессий репликации.
-- Счетчики сессии накопительные: сравниваем прирост.
DECLARE @sample int = 1;

WHILE @sample <= 2
BEGIN
    SELECT
        N'02_REPLICATION_WORK' AS section,
        @sample AS sample_no,
        SYSDATETIME() AS captured_at,
        s.session_id,
        s.login_time,
        s.status AS session_status,
        s.cpu_time AS session_cpu_ms,
        s.logical_reads AS session_logical_reads,
        s.reads AS session_reads,
        s.writes AS session_writes,
        r.status AS request_status,
        r.command,
        r.wait_type,
        r.wait_time AS wait_ms,
        r.blocking_session_id,
        r.wait_resource,
        r.total_elapsed_time AS request_elapsed_ms,
        CASE WHEN r.sql_handle IS NOT NULL THEN
            SUBSTRING(
                txt.text,
                r.statement_start_offset / 2 + 1,
                (
                    CASE r.statement_end_offset
                        WHEN -1 THEN DATALENGTH(txt.text)
                        ELSE r.statement_end_offset
                    END - r.statement_start_offset
                ) / 2 + 1
            )
        END AS current_statement
    FROM sys.dm_exec_sessions AS s
    LEFT 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 txt
    WHERE s.program_name =
          N'WIN-5ODCFIGQRP3_myNewVasya_V3Trans';

    -- Одна и та же transaction_id в обоих снимках,
    -- ее возраст и изменение объема журнала.
    SELECT
        N'03_ACTIVE_TRANSACTIONS' AS section,
        @sample AS sample_no,
        SYSDATETIME() AS captured_at,
        s.session_id,
        st.transaction_id,
        at.transaction_begin_time,
        DATEDIFF(
            SECOND, at.transaction_begin_time, GETDATE()
        ) AS transaction_age_seconds,
        dt.database_transaction_log_record_count AS log_records,
        CAST(
            dt.database_transaction_log_bytes_used / 1048576.0
            AS decimal(18, 2)
        ) AS log_used_mb
    FROM sys.dm_exec_sessions AS s
    JOIN sys.dm_tran_session_transactions AS st
        ON st.session_id = s.session_id
    JOIN sys.dm_tran_active_transactions AS at
        ON at.transaction_id = st.transaction_id
    JOIN sys.dm_tran_database_transactions AS dt
        ON dt.transaction_id = st.transaction_id
    WHERE s.program_name =
          N'WIN-5ODCFIGQRP3_myNewVasya_V3Trans'
      AND dt.database_id = DB_ID();

    -- Есть ли очередь задач на CPU в момент снимка.
    SELECT
        N'04_CPU_QUEUE' AS section,
        @sample AS sample_no,
        scheduler_id,
        runnable_tasks_count,
        current_tasks_count,
        pending_disk_io_count
    FROM sys.dm_os_schedulers
    WHERE status = N'VISIBLE ONLINE';

    IF @sample = 1
        WAITFOR DELAY '00:00:15';

    SET @sample += 1;
END;

-- 3. Последние сообщения доставки.
SELECT TOP (10)
    N'05_DISTRIBUTION_HISTORY' AS section,
    h.[time],
    h.runstatus,
    h.comments
FROM distribution.dbo.MSdistribution_agents AS a
JOIN distribution.dbo.MSdistribution_history AS h
    ON h.agent_id = a.id
WHERE a.publisher_db = N'myNewVasya'
  AND a.publication = N'V3Trans'
  AND a.subscriber_db = N'replTransVas'
ORDER BY h.[time] DESC;

-- 4. Параметры профиля именно этого агента.
SELECT
    N'06_AGENT_PROFILE' AS section,
    a.id AS agent_id,
    a.name AS agent_name,
    a.profile_id,
    p.parameter_name,
    p.value
FROM distribution.dbo.MSdistribution_agents AS a
LEFT JOIN msdb.dbo.MSagent_parameters AS p
    ON p.profile_id = a.profile_id
WHERE a.publisher_db = N'myNewVasya'
  AND a.publication = N'V3Trans'
  AND a.subscriber_db = N'replTransVas'
ORDER BY p.parameter_name;