---

USE [distribution];
SET NOCOUNT ON;

DECLARE @sample int = 1;

WHILE @sample <= 2
BEGIN
    SELECT
        N'00_SAMPLE' AS section,
        @sample AS sample_no,
        SYSDATETIME() AS captured_at,
        @@SERVERNAME AS server_name;

    -- 1. Размер очереди именно нужной подписки.
    EXEC dbo.sp_replmonitorsubscriptionpendingcmds
        @publisher         = N'WIN-5ODCFIGQRP3\SQL2016SQL',
        @publisher_db      = N'myNewVasya',
        @publication       = N'V3Trans',
        @subscriber        = N'WIN-5ODCFIGQRP3\SQL2016SQL',
        @subscriber_db     = N'replTransVas',
        @subscription_type = 0;

    -- 2. Последние сообщения Distribution Agent.
    -- delivered_commands сравниваем только внутри одного start_time.
    SELECT
        N'01_DISTRIBUTION' AS section,
        a.id AS agent_id,
        a.name AS agent_name,
        a.subscriber_db,
        h.[time] AS history_time,
        h.start_time,
        h.runstatus,
        CASE h.runstatus
            WHEN 1 THEN N'START'
            WHEN 2 THEN N'SUCCEED'
            WHEN 3 THEN N'IN_PROGRESS'
            WHEN 4 THEN N'IDLE'
            WHEN 5 THEN N'RETRY'
            WHEN 6 THEN N'FAIL'
        END AS agent_status,
        h.delivered_commands,
        h.current_delivery_rate AS commands_per_second,
        h.error_id,
        h.comments
    FROM dbo.MSdistribution_agents AS a
    CROSS APPLY
    (
        SELECT TOP (15) hh.*
        FROM dbo.MSdistribution_history AS hh
        WHERE hh.agent_id = a.id
        ORDER BY hh.[time] DESC
    ) AS h
    WHERE a.publisher_db = N'myNewVasya'
      AND a.publication = N'V3Trans'
      AND a.subscriber_db = N'replTransVas'
    ORDER BY a.id, h.[time] DESC;

    -- 3. История чтения журнала основной базы.
    SELECT
        N'02_LOG_READER' AS section,
        a.name AS agent_name,
        h.[time] AS history_time,
        h.runstatus,
        h.delivered_commands,
        h.delivery_rate,
        h.error_id,
        h.comments
    FROM dbo.MSlogreader_agents AS a
    CROSS APPLY
    (
        SELECT TOP (10) hh.*
        FROM dbo.MSlogreader_history AS hh
        WHERE hh.agent_id = a.id
        ORDER BY hh.[time] DESC
    ) AS h
    WHERE a.publisher_db = N'myNewVasya'
    ORDER BY a.id, h.[time] DESC;

    -- 4. Текущие запросы, ожидания и непосредственные блокировщики.
    ;WITH relevant AS
    (
        SELECT s.session_id
        FROM sys.dm_exec_sessions AS s
        LEFT JOIN sys.dm_exec_requests AS r
            ON r.session_id = s.session_id
        WHERE s.session_id <> @@SPID
          AND
          (
              s.program_name LIKE N'%Repl%'
              OR s.program_name LIKE N'%V3Trans%'
              OR r.database_id IN
                 (
                     DB_ID(N'myNewVasya'),
                     DB_ID(N'replTransVas'),
                     DB_ID(N'distribution')
                 )
          )
    ),
    wanted AS
    (
        SELECT session_id FROM relevant
        UNION
        SELECT r.blocking_session_id
        FROM sys.dm_exec_requests AS r
        JOIN relevant AS x ON x.session_id = r.session_id
        WHERE r.blocking_session_id > 0
    )
    SELECT
        N'03_REQUESTS_AND_BLOCKERS' AS section,
        s.session_id,
        s.program_name,
        s.login_name,
        s.status AS session_status,
        DB_NAME(r.database_id) AS database_name,
        r.status AS request_status,
        r.command,
        r.blocking_session_id,
        r.wait_type,
        r.wait_time AS wait_ms,
        r.wait_resource,
        r.total_elapsed_time AS elapsed_ms,
        r.cpu_time AS request_cpu_ms,
        r.logical_reads,
        r.reads,
        r.writes,
        s.open_transaction_count,
        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
                )
            ELSE txt.text
        END AS current_or_last_sql
    FROM wanted AS w
    JOIN sys.dm_exec_sessions AS s
        ON s.session_id = w.session_id
    LEFT JOIN sys.dm_exec_requests AS r
        ON r.session_id = s.session_id
    LEFT JOIN sys.dm_exec_connections AS c
        ON c.session_id = s.session_id
    OUTER APPLY sys.dm_exec_sql_text(
        COALESCE(r.sql_handle, c.most_recent_sql_handle)
    ) AS txt
    ORDER BY r.blocking_session_id DESC, s.session_id;

    -- 5. Состояние SQL Agent job нужной подписки.
    SELECT
        N'04_AGENT_JOB' AS section,
        j.name,
        j.enabled,
        ja.start_execution_date,
        ja.stop_execution_date,
        CASE
            WHEN ja.start_execution_date IS NOT NULL
             AND ja.stop_execution_date IS NULL
                THEN N'RUNNING'
            ELSE N'NOT_RUNNING'
        END AS job_state
    FROM msdb.dbo.sysjobs AS j
    LEFT JOIN msdb.dbo.sysjobactivity AS ja
        ON ja.job_id = j.job_id
       AND ja.session_id =
           (SELECT MAX(session_id) FROM msdb.dbo.syssessions)
    WHERE j.name LIKE N'%V3Trans%';

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

    SET @sample += 1;
END;

-- 6. Последние ошибки именно нужного Distribution Agent.
SELECT TOP (20)
    N'05_DISTRIBUTION_ERRORS' AS section,
    a.name AS agent_name,
    h.[time] AS history_time,
    h.runstatus,
    h.comments,
    e.*
FROM dbo.MSdistribution_agents AS a
JOIN dbo.MSdistribution_history AS h
    ON h.agent_id = a.id
JOIN dbo.MSrepl_errors AS e
    ON e.id = h.error_id
WHERE a.publisher_db = N'myNewVasya'
  AND a.publication = N'V3Trans'
  AND a.subscriber_db = N'replTransVas'
ORDER BY h.[time] DESC;