---

USE distribution;
SET NOCOUNT ON;

DECLARE
    @Publisher sysname = N'WIN-5ODCFIGQRP3\SQL2016SQL',
    @Subscriber sysname = N'WIN-5ODCFIGQRP3\SQL2016SQL',
    @Publication sysname = N'V3Trans',
    @SubscriptionType int,
    @AgentId int,
    @Matches int;

-- Находим конкретную подписку.
SELECT
    @Matches = COUNT(*),
    @AgentId = MAX(id),
    @SubscriptionType = MAX(subscription_type)
FROM dbo.MSdistribution_agents
WHERE publisher_db = N'myNewVasya'
  AND subscriber_db = N'replTransVas'
  AND publication = @Publication;

IF @Matches <> 1
BEGIN
    SELECT
        id, name, publisher_db, publication,
        subscriber_db, subscription_type
    FROM dbo.MSdistribution_agents
    WHERE publisher_db = N'myNewVasya';

    RAISERROR(
        N'Не удалось однозначно определить подписку V3Trans. Пришлите результат.',
        16, 1
    );
    RETURN;
END;

-- 1. Текущие сообщения именно этого агента.
SELECT TOP (20)
    h.[time],
    h.runstatus,
    h.comments,
    h.delivered_transactions,
    h.delivered_commands,
    h.delivery_latency,
    h.error_id
FROM dbo.MSdistribution_history AS h
WHERE h.agent_id = @AgentId
ORDER BY h.[time] DESC;

-- 2. Первый замер очереди.
SELECT N'ОЧЕРЕДЬ ДО' AS stage, GETDATE() AS checkedAt;

EXEC dbo.sp_replmonitorsubscriptionpendingcmds
    @publisher = @Publisher,
    @publisher_db = N'myNewVasya',
    @publication = @Publication,
    @subscriber = @Subscriber,
    @subscriber_db = N'replTransVas',
    @subscription_type = @SubscriptionType;

WAITFOR DELAY '00:00:30';

-- 3. Второй замер очереди.
SELECT N'ОЧЕРЕДЬ ПОСЛЕ' AS stage, GETDATE() AS checkedAt;

EXEC dbo.sp_replmonitorsubscriptionpendingcmds
    @publisher = @Publisher,
    @publisher_db = N'myNewVasya',
    @publication = @Publication,
    @subscriber = @Subscriber,
    @subscriber_db = N'replTransVas',
    @subscription_type = @SubscriptionType;

-- 4. Последние сообщения после ожидания.
SELECT TOP (10)
    h.[time],
    h.runstatus,
    h.comments,
    h.delivered_transactions,
    h.delivered_commands,
    h.delivery_latency,
    h.error_id
FROM dbo.MSdistribution_history AS h
WHERE h.agent_id = @AgentId
ORDER BY h.[time] DESC;