允许Server作业在结束之前运行特定时间

Question

Server 2016标准版

我们有一个清理作业，运行一些非常简单的sql：

use productionDB
go

delete from transactionaltable where 
createdat < DATEADD(day, -21, GETDATE()) 
go

这是每个工作日的晚上，而且一直很棒.直到昨晚，当一个(明显的)锁升级或冲突的作业导致它挂起，锁定桌子，造成各种各样的混乱。

我心里想：“嘿，我一定能在工作上或台阶上施点魔法，比如‘跑了十分钟就杀了人’。”但我找不到。

是唯一的技术：

• 在0100解雇真正的工作
• 在0110 //一个很少被击中的安全装置上开火。

使用此技术构建的杀伤任务：

USE msdb ;
GO
EXEC dbo.sp_stop_job N'Your Job Name' ;
GO

还是有更好/更清洁的方法来做到这一点？

溶液

停止使用粗劣的WHERE语句，并使用类似于这里的方法，开始在小批中删除行。

Answer 1

我在SQL代理作业上使用了以下存储的proc：

    -- =============================================
    -- Author:        Devin Knight and Jorge Segarra
    -- Create date: 7/6/2012
    -- Description:    Monitors currently running SQL Agent jobs and 
    -- alerts admins if runtime passes set threshold
    --          
-- =============================================

/*
Change log:
 =============================================
7/11/2012 (v 1.01)  
        Changed Method for capturing currently running jobs to use master.dbo.xp_sqlagent_enum_jobs 1, ''

7/12/2012 (v 1.03)
        Updated code to deal with “phantom” jobs that weren’t really running. 
        Improved logic to handle this. Beware, uses undocumented stored procedure xp_sqlagent_enum_jobs


7/24/2012 (v. 1.16)
        Removed need to specify mail profile
        Fix for error sending notify email
        Added commented line for testing purposes (avg+1 minute for short tests)
 =============================================
 exec usp_LongRunningJobs
*/
ALTER PROCEDURE [dbo].[usp_LongRunningJobs] 
                @jobname nvarchar(100)
AS 

--Set limit in minutes (applies to all jobs)
--NOTE: Percentage limit is applied to all jobs where average runtime greater than 5 minutes
--else the time limit is simply average + 10 minutes
    DECLARE @JobLimitPercentage FLOAT

    SET @JobLimitPercentage = 150 --Use whole percentages greater than 100

    -- Create intermediate work tables for currently running jobs
    DECLARE @currently_running_jobs TABLE
        (
          job_id UNIQUEIDENTIFIER NOT NULL ,
          last_run_date INT NOT NULL ,
          last_run_time INT NOT NULL ,
          next_run_date INT NOT NULL ,
          next_run_time INT NOT NULL ,
          next_run_schedule_id INT NOT NULL ,
          requested_to_run INT NOT NULL ,-- BOOL
          request_source INT NOT NULL ,
          request_source_id SYSNAME COLLATE database_default
                                    NULL ,
          running INT NOT NULL ,-- BOOL
          current_step INT NOT NULL ,
          current_retry_attempt INT NOT NULL ,
          job_state INT NOT NULL
        ) -- 0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, [6 = WaitingForStepToFinish], 7 = PerformingCompletionActions

--Capture Jobs currently working
    INSERT  INTO @currently_running_jobs
            EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, ''

--Temp table exists check


    CREATE TABLE ##LRJobsStage
        (
          [JobID] [UNIQUEIDENTIFIER] NOT NULL ,
          [JobName] [sysname] NOT NULL ,
          [StartExecutionDate] [DATETIME] NOT NULL ,
          [AvgDurationMin] [INT] NULL ,
          [DurationLimit] [INT] NULL ,
          [CurrentDuration] [INT] NULL
        )

    INSERT  INTO ##LRJobsStage
            ( JobID ,
              JobName ,
              StartExecutionDate ,
              AvgDurationMin ,
              DurationLimit ,
              CurrentDuration
            )
            SELECT  jobs.Job_ID AS JobID ,
                    jobs.NAME AS JobName ,
                    act.start_execution_date AS StartExecutionDate ,
                    AVG(FLOOR(run_duration / 100)) AS AvgDurationMin ,
                    CASE 
        --If job average less than 5 minutes then limit is avg+10 minutes
                         WHEN AVG(FLOOR(run_duration / 100)) <= 5
                         THEN ( AVG(FLOOR(run_duration / 100)) ) + 10
        --If job average greater than 5 minutes then limit is avg*limit percentage
                         ELSE ( AVG(FLOOR(run_duration / 100))
                                * ( @JobLimitPercentage / 100 ) )
                    END AS DurationLimit ,
                    DATEDIFF(MI, act.start_execution_date, GETDATE()) AS [CurrentDuration]
            FROM    @currently_running_jobs crj
                    INNER JOIN msdb..sysjobs AS jobs ON crj.job_id = jobs.job_id
                    INNER JOIN msdb..sysjobactivity AS act ON act.job_id = crj.job_id
                                                              AND act.stop_execution_date IS NULL
                                                              AND act.start_execution_date IS NOT NULL
                    INNER JOIN msdb..sysjobhistory AS hist ON hist.job_id = crj.job_id
                                                              AND hist.step_id = 0
            WHERE   crj.job_state = 1
            and jobs.NAME=@jobname
            GROUP BY jobs.job_ID ,
                    jobs.NAME ,
                    act.start_execution_date ,
                    DATEDIFF(MI, act.start_execution_date, GETDATE())
            HAVING  CASE WHEN AVG(FLOOR(run_duration / 100)) <= 5
                              THEN (AVG(FLOOR(run_duration / 100))) + 10
                            --THEN ( AVG(FLOOR(run_duration / 100)) ) + 1  --Uncomment/Use for testing purposes only
                         ELSE ( AVG(FLOOR(run_duration / 100))
                                * ( @JobLimitPercentage / 100 ) )
                    END < DATEDIFF(MI, act.start_execution_date, GETDATE())


--Checks to see if a long running job has already been identified so you are not alerted multiple times
    IF EXISTS ( SELECT  RJ.*
                FROM    ##LRJobsStage RJ
                WHERE   CHECKSUM(RJ.JobID, RJ.StartExecutionDate) NOT IN (
                        SELECT  CHECKSUM(JobID, StartExecutionDate)
                        FROM    dbo.LongRunningJobs ) )
     BEGIN
--Send email with results of long-running jobs

    --Set Email Recipients
        DECLARE @MailRecipients VARCHAR(200)

    SET @MailRecipients = 'developer@adventureworks.com' --Uncomment/Use for testing purposes only

    EXEC msdb.dbo.sp_send_dbmail --@profile_name = @MailProfile
        @recipients = @MailRecipients,
        @query = 'USE DB_Maintenance; Select RJ.*
                From ##LRJobsStage RJ
                WHERE CHECKSUM(RJ.JobID,RJ.StartExecutionDate) 
                NOT IN (Select CHECKSUM(JobID,StartExecutionDate) From dbo.LongRunningJobs) ',
        @body = 'View attachment to view long running jobs',
        @subject = 'Long Running SQL Agent Job Alert',
        @attach_query_result_as_file = 1;

--Populate LongRunningJobs table with jobs exceeding established limits
    INSERT  INTO DB_Maintenance.[dbo].[LongRunningJobs]
            ( [JobID] ,
              [JobName] ,
              [StartExecutionDate] ,
              [AvgDurationMin] ,
              [DurationLimit] ,
              [CurrentDuration]
            )
            ( SELECT    RJ.*
              FROM      ##LRJobsStage RJ
              WHERE     CHECKSUM(RJ.JobID, RJ.StartExecutionDate) NOT IN (
                        SELECT  CHECKSUM(JobID, StartExecutionDate)
                        FROM    dbo.LongRunningJobs )
            )
    END

编辑以包括创建表：

CREATE TABLE [dbo].[LongRunningJobs](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [JobName] [sysname] NOT NULL,
    [JobID] [uniqueidentifier] NOT NULL,
    [StartExecutionDate] [datetime] NULL,
    [AvgDurationMin] [int] NULL,
    [DurationLimit] [int] NULL,
    [CurrentDuration] [int] NULL,
    [RowInsertDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[LongRunningJobs] ADD  CONSTRAINT [DF_LongRunningJobs_Date]  DEFAULT (getdate()) FOR [RowInsertDate]
GO

Answer 2

一种黑客方法是将脚本作为CmdExec步骤运行，然后运行sqlcmd并设置连接超时。(未经测试，但我认为，如果没有其他选择，我认为会奏效。)

Answer 3

几件事。

• 您使用的术语是“死锁”，但这很可能只是阻塞，因为死锁在Server中是自动解析的。
• 为等待锁设置时间上限的内置方法是使用"SET LOCK_TIMEOUT“语句。例如，请参见：(https://learn.microsoft.com/en-us/sql/t-sql/statements/set-lock-timeout-transact-sql?view=sql-server-ver15)，假设您希望等待不超过1分钟:将LOCK_TIMEOUT 60000从创建
• 似乎你试图修补一个问题，也许应该以不同的方式来解决。您考虑过使用分区切换和截断来删除这些记录吗？(在v2016-SP1中，Server标准版中提供了表分区。)这应该完全避免原来的问题。