---

USE msdb;
SET NOCOUNT ON;

DECLARE @DatabaseName sysname = N'myNewVasya';
DECLARE @StartStoppedAgents bit = 1; -- 0 = только диагностика

/* 1. Состояние SQL Server Agent */
BEGIN TRY
    SELECT
        servicename,
        status_desc,
        startup_type_desc,
        last_startup_time
    FROM sys.dm_server_services
    WHERE servicename LIKE N'SQL Server Agent%';
END TRY
BEGIN CATCH
    SELECT N'Проверка службы' AS stage, ERROR_MESSAGE() AS errorMessage;
END CATCH;

/* 2. Находим задания.
   Отбираем Log Reader и Distribution, связанные с указанной БД. */
IF OBJECT_ID(N'tempdb..#ReplicationJobs') IS NOT NULL
    DROP TABLE #ReplicationJobs;

SELECT DISTINCT
    j.job_id,
    j.name AS jobName,
    c.name AS categoryName,
    j.enabled
INTO #ReplicationJobs
FROM dbo.sysjobs AS j
JOIN dbo.syscategories AS c
    ON c.category_id = j.category_id
WHERE c.name IN (N'REPL-Distribution', N'REPL-LogReader')
  AND
  (
      CHARINDEX(@DatabaseName, j.name) > 0
      OR EXISTS
      (
          SELECT 1
          FROM dbo.sysjobsteps AS s
          WHERE s.job_id = j.job_id
            AND CHARINDEX(@DatabaseName, s.command) > 0
      )
  );

DECLARE @AgentSessionId int =
    (SELECT MAX(session_id) FROM dbo.syssessions);

SELECT
    N'ДО ЗАПУСКА' AS stage,
    j.jobName,
    j.categoryName,
    j.enabled,
    a.start_execution_date,
    a.stop_execution_date,
    CASE
        WHEN a.start_execution_date IS NOT NULL
         AND a.stop_execution_date IS NULL
            THEN N'RUNNING'
        ELSE N'NOT RUNNING'
    END AS agentState
FROM #ReplicationJobs AS j
LEFT JOIN dbo.sysjobactivity AS a
    ON a.job_id = j.job_id
   AND a.session_id = @AgentSessionId
ORDER BY j.categoryName, j.jobName;

/* 3. Последние сообщения выбранных заданий */
SELECT TOP (60)
    j.jobName,
    h.step_id,
    h.step_name,
    h.run_date,
    h.run_time,
    h.run_duration,
    CASE h.run_status
        WHEN 0 THEN N'FAILED'
        WHEN 1 THEN N'SUCCEEDED'
        WHEN 2 THEN N'RETRY'
        WHEN 3 THEN N'CANCELED'
        WHEN 4 THEN N'IN PROGRESS'
    END AS runStatus,
    h.message
FROM dbo.sysjobhistory AS h
JOIN #ReplicationJobs AS j
    ON j.job_id = h.job_id
ORDER BY h.instance_id DESC;

/* 4. Последние ошибки распространителя.
   Это общий журнал: ошибки могут относиться к другим публикациям. */
IF DB_ID(N'distribution') IS NOT NULL
BEGIN
    BEGIN TRY
        EXEC sys.sp_executesql N'
            SELECT TOP (30)
                [time],
                error_code,
                error_text
            FROM distribution.dbo.MSrepl_errors
            ORDER BY [time] DESC, id DESC;';
    END TRY
    BEGIN CATCH
        SELECT
            N'Ошибки distribution' AS stage,
            ERROR_MESSAGE() AS errorMessage;
    END CATCH;
END
ELSE
BEGIN
    SELECT N'База distribution здесь не найдена. Журнал ошибок нужно '
         + N'проверить на распространителе либо в БД распространения '
         + N'с другим именем.' AS diagnosticMessage;
END;

/* 5. Запускаем только остановленные включённые задания.
   Сначала Log Reader, затем Distribution.
   Отключённые задания автоматически не включаем. */
DECLARE
    @JobId uniqueidentifier,
    @JobName sysname;

DECLARE agent_cursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT job_id, jobName
    FROM #ReplicationJobs
    WHERE enabled = 1
    ORDER BY
        CASE categoryName
            WHEN N'REPL-LogReader' THEN 0
            ELSE 1
        END,
        jobName;

IF @StartStoppedAgents = 1
BEGIN
    OPEN agent_cursor;
    FETCH NEXT FROM agent_cursor INTO @JobId, @JobName;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Проверяем актуальный сеанс перед каждым запуском.
        SELECT @AgentSessionId = MAX(session_id)
        FROM dbo.syssessions;

        IF NOT EXISTS
        (
            SELECT 1
            FROM dbo.sysjobactivity
            WHERE job_id = @JobId
              AND session_id = @AgentSessionId
              AND start_execution_date IS NOT NULL
              AND stop_execution_date IS NULL
        )
        BEGIN
            BEGIN TRY
                EXEC dbo.sp_start_job @job_id = @JobId;

                SELECT
                    @JobName AS jobName,
                    N'Команда запуска принята' AS actionResult;
            END TRY
            BEGIN CATCH
                SELECT
                    @JobName AS jobName,
                    N'Ошибка запуска' AS actionResult,
                    ERROR_MESSAGE() AS errorMessage;
            END CATCH;
        END
        ELSE
        BEGIN
            SELECT
                @JobName AS jobName,
                N'Уже работает — без изменений' AS actionResult;
        END;

        FETCH NEXT FROM agent_cursor INTO @JobId, @JobName;
    END;

    CLOSE agent_cursor;

    -- Небольшая пауза для появления результата запуска.
    WAITFOR DELAY '00:00:05';
END;

DEALLOCATE agent_cursor;

/* 6. Состояние после запуска */
SELECT @AgentSessionId = MAX(session_id)
FROM dbo.syssessions;

SELECT
    N'ИТОГ' AS stage,
    j.jobName,
    j.categoryName,
    currentJob.enabled,
    a.start_execution_date,
    a.stop_execution_date,
    CASE
        WHEN a.start_execution_date IS NOT NULL
         AND a.stop_execution_date IS NULL
            THEN N'RUNNING'
        ELSE N'NOT RUNNING'
    END AS agentState,
    lastHistory.run_date AS lastHistoryDate,
    lastHistory.run_time AS lastHistoryTime,
    lastHistory.message AS lastHistoryMessage
FROM #ReplicationJobs AS j
JOIN dbo.sysjobs AS currentJob
    ON currentJob.job_id = j.job_id
LEFT JOIN dbo.sysjobactivity AS a
    ON a.job_id = j.job_id
   AND a.session_id = @AgentSessionId
OUTER APPLY
(
    SELECT TOP (1)
        h.run_date,
        h.run_time,
        h.message
    FROM dbo.sysjobhistory AS h
    WHERE h.job_id = j.job_id
    ORDER BY h.instance_id DESC
) AS lastHistory
ORDER BY j.categoryName, j.jobName;

IF NOT EXISTS (SELECT 1 FROM #ReplicationJobs)
BEGIN
    SELECT N'Задания не найдены. Проверьте сервер выполнения: '
         + N'Log Reader находится на распространителе, '
         + N'Distribution для pull-подписки — на подписчике.'
         AS diagnosticMessage;
END;

SELECT
    jobName,
    N'Задание отключено. Автоматический запуск пропущен.'
        AS diagnosticMessage
FROM #ReplicationJobs
WHERE enabled = 0;

DROP TABLE #ReplicationJobs;