sql代理作业超时自动重启

使用另一个作业定期检查您的作业是否正在运行、它在哪个步骤运行以及超时值是多少。您可以通过在作业中运行存储过程来完成此操作。我为您编写了存储过程，如果您有任何问题，请发表评论:)

-- =============================================
-- Author:      <Wynants Stijn>
-- Create date: <30/03/2016>
-- Description: <GetHangingJob and Restart at step>
-- =============================================
CREATE PROCEDURE dbo.usp_GetHangingJob
    -- Add the parameters for the stored procedure here
    @JobName varchar(1000),
    @TimeOut int   ---In minutes
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @JobState varchar(20)
    DECLARE @JobStep int
    DECLARE @JobStepName varchar(1000)
    SET @JobState = (select top 1 
        case when
        case when last_executed_step_date is null then a.run_requested_date else last_executed_step_date end > DATEADD(mi,-@TimeOut,getdate()) THEN 'OK' else 'NOT OK' end as JobState
    from 
        msdb.dbo.sysjobs_view j
    inner join 
        msdb.dbo.sysjobactivity a 
    on 
        j.job_id = a.job_id
    where 
        a.run_requested_date is not null
    and 
        a.stop_execution_date is null
    and
        j.name = @JobName)

    SET @JobStep = (select case when last_executed_step_id is null then 1 else last_executed_step_id + 1 end as CurrentJobStep 
    FROM
            msdb.dbo.sysjobs_view j
    inner join 
        msdb.dbo.sysjobactivity a 
    on 
        j.job_id = a.job_id
    where 
        a.run_requested_date is not null
    and 
        a.stop_execution_date is null
    and
        j.name = @JobName)

    SET @JobStepName = (select top 1 s.step_name from msdb.dbo.sysjobs j inner join msdb.dbo.sysjobsteps s on j.job_id = s.job_id and s.step_id = @JobStep and j.name = @JobName)
    IF @JobState = 'NOT OK'
    BEGIN
        exec msdb.dbo.sp_stop_job @JobName
        WAITFOR DELAY '00:00:02'
        exec msdb.dbo.sp_start_job @JobName,NULL,NULL,NULL,@JobStepName,NULL
    END


END
GO

享受！