主页 / dba / 问题 / 298011
Accepted
Manu
Asked: 2021-08-14 03:25:53 +0800 CST

在特定时间停止其他作业的作业

  • 772

我们有一个每天晚上从远程 SQL Server 导入数据的工作，我称之为 Import-Job。大多数情况下，这项工作在大约 15 分钟内完成，一切都很好，但由于 VPN 连接相当不稳定，有时这项工作需要更多时间。

我们为这项工作指定了 1.5 小时的时间窗口，我们不能冒险让 Import-Job 超过这个窗口（其他工作和生产从某个时间点开始）。

我们在时间窗口结束时使用另一个执行以下 TSQL 语句的作业停止 Import-Job：

IF EXISTS (
            SELECT 1 
            FROM msdb.dbo.sysjobs J 
            JOIN msdb.dbo.sysjobactivity A 
            ON A.job_id=J.job_id 
            WHERE J.name=N'Import-Job' 
            AND A.run_requested_date IS NOT NULL 
            AND A.stop_execution_date IS NULL
    )
    BEGIN
            EXEC msdb.dbo.sp_stop_job
            N'Import-Job';
        PRINT 'The job was stopped.'
    END
ELSE
    BEGIN
            PRINT 'The job is not running.'
    END

这确实有效，但它每次都会尝试停止 Import-Job，即使 Import-Job 没有运行，也会导致 Job History 中出现错误：

QLServerAgent Error: Request to stop job Import-Job (from User ServiceUser) refused because the job is not currently running.

我在这个语句中遗漏了什么，我如何让这个 T-SQL 脚本工作，这样它就不会在 Import-Job 没有运行时尝试停止它？（我想摆脱错误）

sql-server sql-server-agent

1 个回答

  1. Best Answer

    如果你SELECT在你的 中运行IF EXISTS，我想你会发现它至少返回一行（可能是几行），即使你的工作没有运行

    SELECT 1 
FROM msdb.dbo.sysjobs J 
JOIN msdb.dbo.sysjobactivity A 
ON A.job_id=J.job_id 
WHERE J.name=N'Import-Job' 
AND A.run_requested_date IS NOT NULL 
AND A.stop_execution_date IS NULL

    这样做的原因是，如果 SQL 代理进程在作业运行时意外终止，它将永远不会填充结束日期（在作业运行时重新启动服务器时可能会发生这种情况）。在您的代码中显示旧作业A.run_requested_date IS NOT NULL AND A.stop_execution_date IS NULL应该是常见且预期的。

    许多人xp_sqlagent_enum_jobs用来获取工作当前状态，但是这是一个未记录的扩展程序，所以我倾向于不使用它。xp_sqlagent_enum_jobs我在这篇博文中进一步解释了我不喜欢的原因。

    为了大致xp_sqlagent_enum_jobs完成使用 T-SQL 所做的事情，我使用了这样的东西：

        SELECT TOP 1
        IsRunning = CASE WHEN ja.job_id IS NOT NULL AND ja.stop_execution_date IS NULL THEN 1 ELSE 0 END,
        LastRunTime = ja.start_execution_date,
        NextRunTime = ja.next_scheduled_run_date,
        LastJobStep = js.step_name,
        JobOutcome = CASE 
                        WHEN ja.job_id IS NOT NULL AND ja.stop_execution_date IS NULL THEN 'Running'
                        WHEN run_status = 0 THEN 'Failed'
                        WHEN run_status = 1 THEN 'Succeeded'
                        WHEN run_status = 2 THEN 'Retry'
                        WHEN run_status = 3 THEN 'Cancelled'
                    END
    FROM msdb.dbo.sysjobs j
    LEFT JOIN msdb.dbo.sysjobactivity ja 
        ON ja.job_id = j.job_id
        AND ja.run_requested_date IS NOT NULL
        AND ja.start_execution_date IS NOT NULL
    LEFT JOIN msdb.dbo.sysjobsteps js
        ON js.job_id = ja.job_id
        AND js.step_id = ja.last_executed_step_id
    LEFT JOIN msdb.dbo.sysjobhistory jh
        ON jh.job_id = j.job_id
        AND jh.instance_id = ja.job_history_id
    WHERE j.name = @JobName
    ORDER BY ja.start_execution_date DESC;

    作为我的开源DBA 数据库的一部分，这个xp_agent_enum_jobs替代方案在 GitHub 上作为一个函数提供。

    使用该函数，您的代理作业步骤将如下所示：

    IF EXISTS (
            SELECT 1 
            FROM msdb.dbo.sysjobs j
            CROSS APPLY dbo.AgentJob_Status (j.name) sts
            WHERE j.name = 'Import-Job'
            AND sts.IsRunning = 1
    )
    BEGIN
            EXEC msdb.dbo.sp_stop_job
            N'Import-Job';
        PRINT 'The job was stopped.'
    END
ELSE
    BEGIN
            PRINT 'The job is not running.'
    END

    或者，如果您只想从函数中提取必要的代码并将其内联到您的工作步骤中，您可以执行以下操作：请注意，主要的功能差异（解决上面提到的“旧行”问题）是添加TOP 1...ORDER BY一个SELECT：

    IF EXISTS (
            SELECT TOP 1 1 
            FROM msdb.dbo.sysjobs j
            LEFT JOIN msdb.dbo.sysjobactivity ja 
                ON ja.job_id = j.job_id
                AND ja.run_requested_date IS NOT NULL
                AND ja.start_execution_date IS NOT NULL
            LEFT JOIN msdb.dbo.sysjobsteps js
                ON js.job_id = ja.job_id
                AND js.step_id = ja.last_executed_step_id
            LEFT JOIN msdb.dbo.sysjobhistory jh
                ON jh.job_id = j.job_id
                AND jh.instance_id = ja.job_history_id
            WHERE j.name = 'Import-Job'
            AND ja.job_id IS NOT NULL 
            AND ja.stop_execution_date IS NULL
            ORDER BY ja.start_execution_date DESC
    )
    BEGIN
            EXEC msdb.dbo.sp_stop_job
            N'Import-Job';
        PRINT 'The job was stopped.'
    END
ELSE
    BEGIN
            PRINT 'The job is not running.'
    END
    • 4

相关问题