---

USE [replTransVas];
SET NOCOUNT ON;

IF CONVERT(sysname, SERVERPROPERTY('ServerName'))
    <> N'WIN-5ODCFIGQRP3\SQL2016SQL'
BEGIN
    ;THROW 50001, N'Неверный экземпляр SQL Server.', 1;
END;

IF @@TRANCOUNT <> 0
BEGIN
    ;THROW 50002, N'Запустите блок вне открытой транзакции.', 1;
END;

DECLARE @obj int = OBJECT_ID(N'dbo.sb_answer', N'U');

IF @obj IS NULL
BEGIN
    ;THROW 50003, N'Таблица dbo.sb_answer не найдена.', 1;
END;

-- Допускаются только два обычных неуникальных
-- некластерных индекса.
IF
(
    SELECT COUNT(*)
    FROM sys.indexes
    WHERE object_id = @obj
      AND name IN
      (
          N'IX_sb_answer_MtsMemberLatest',
          N'IX_sb_answer_MtsParticipantLatest'
      )
      AND type = 2
      AND is_unique = 0
      AND is_primary_key = 0
      AND is_unique_constraint = 0
      AND is_hypothetical = 0
) <> 2
BEGIN
    ;THROW 50004,
        N'Состав индексов отличается от ожидаемого. Изменения отменены.',
        1;
END;

BEGIN TRY
    EXEC sys.sp_executesql N'
        SET LOCK_TIMEOUT 5000;

        IF EXISTS
        (
            SELECT 1
            FROM sys.indexes
            WHERE object_id = OBJECT_ID(N''dbo.sb_answer'')
              AND name = N''IX_sb_answer_MtsMemberLatest''
              AND is_disabled = 0
        )
            ALTER INDEX [IX_sb_answer_MtsMemberLatest]
                ON dbo.sb_answer DISABLE;

        IF EXISTS
        (
            SELECT 1
            FROM sys.indexes
            WHERE object_id = OBJECT_ID(N''dbo.sb_answer'')
              AND name = N''IX_sb_answer_MtsParticipantLatest''
              AND is_disabled = 0
        )
            ALTER INDEX [IX_sb_answer_MtsParticipantLatest]
                ON dbo.sb_answer DISABLE;
    ';
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS error_number,
        ERROR_MESSAGE() AS error_message;

    SELECT name, type_desc, is_disabled
    FROM sys.indexes
    WHERE object_id = @obj;

    THROW;
END CATCH;

SELECT
    SYSDATETIME() AS checked_at,
    name,
    type_desc,
    is_disabled
FROM sys.indexes
WHERE object_id = @obj
ORDER BY index_id;


USE [distribution];
SET NOCOUNT ON;

SELECT TOP (8)
    h.[time],
    h.runstatus,
    h.comments
FROM dbo.MSdistribution_agents AS a
JOIN 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;

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;