更有效地管理 SQL Server 代理历史记录

---

问题 SQL Server 公开了两个用于管理 SQL Server 代理日志的设置。一个设置指示要保留多少日志行，这被分解为任何单个作业和整个代理子系统的选项。第二个是一个简单的时间间隔，指示历史数据在自动删除之前必须保留多长时间（以天、周或月为单位）：

SQL Server 代理历史设置 这不是很灵活，事实上，您正在阅读本文可能是因为您的工作具有不同的历史保留要求。不仅某些工作比其他工作更重要，您希望保留比其他工作更多的历史，而且您可能还需要某种逻辑来指示年龄或计数哪个更重要。

考虑一个非常简单的完整备份和日志备份案例。如果您每天或每周执行一次完整备份，您真的需要最近 100 个事件的历史记录吗？如果您每 5 分钟备份一次日志，那么只有最后 100 个条目就足够了吗？如果您有 10 个不同的备份作业，或者某些作业有很多步骤，会发生什么情况？他们将开始践踏彼此的历史。

备份不是最好的例子，因为此信息也存储在 msdb 中。然而，这是一个明显的说明：我们如何使代理历史更可定制。

解决方案 在我们找到解决方案之前，让我们更深入地研究一下这个问题。

除了需要作业之间和整个系统之间的灵活性外，您可能还需要作业步骤之间的灵活性。考虑这样一种情况，您有一个包含五个步骤的工作，但其中只有一个是真正重要的（也许其他步骤只是初始化步骤或在成功或失败时发送电子邮件）。在目前的设计中，没有办法说保留第 3 步的历史记录，而不是其他的。如果您认为这只是噪音，那还不止这些：我将最大历史记录行数设置为 10，将每个作业的最大行数设置为 10。然后我创建了两个作业，一个有 1 个步骤，另一个有 5 个。我运行了第一次工作两次，然后第二次工作两次，并检查历史记录。

请注意，作业本身（第 0 步）占用一个历史记录槽，因此对于“5 步作业”的最近执行，实际上有 6 个历史记录条目。由于我们只能为任何单个作业保留 10 个历史记录行，因此较早的执行会被截断。SQL Server 保留第 0 步，但删除第 1 步和第 2 步的历史记录。

如果您只想保留 8 小时或 12 小时的工作历史滚动窗口，那么还有一个问题。也许您的工作在工作时间更频繁地运行，或者按需调用，因此它们特别不稳定和/或受数量驱动。限制到特定的行数或一天边界的最低粒度不能完全满足要求。

最后，在某些情况下，您可能希望保留所有失败，而不管年龄或作业或步骤自那以后成功了多少次，并且您可能希望保留任何超过指定持续时间的作业或步骤（但不是所有的成功或快速运行的其他步骤）。

从所有这些中，我们得到了一个简单的需求列表。我们想要一个工作经历保留政策，使我们能够：

• 定义每个作业和每个步骤的保留历史记录
• 保留尽可能多或尽可能少的行
• 将行保持尽可能短或尽可能长
• 当两者的规则都存在时，决定哪一个“获胜”
• 保留任何失败的步骤的历史记录，即使在我们的规则之外，或者已经超过了某个指定的持续时间

让我们从 msdb 中的一个表开始，该表可以存储有关我们的首选项的详细信息（基本上是保留/删除行的规则结构）。大多数列的命名都是直观的：

USE msdb;
GO
CREATE TABLE dbo.RetentionRules
(
  job_id uniqueidentifier NOT NULL, 
  step_id int NOT NULL,
  NumberOfRowsToKeep int,
  NumberOfHoursToKeep int,
  PreferRowsOrHours char(1),
  KeepStepsThatFailed bit,
  DurationOverrideSeconds int, -- keep any step that ran longer than this
  CONSTRAINT PK_JobStep PRIMARY KEY (job_id, step_id),
  CONSTRAINT CK_RowsOrHours CHECK (PreferRowsOrHours IN ('R','H'))
  /*,
    CONSTRAINT FK_RetentionRules_JobSteps
    FOREIGN KEY(job_id, step_id)  REFERENCES dbo.sysjobsteps(job_id, step_id)
  */
);

您可以在此处放置一个外键，我已将其注释掉，但您可能不希望保留表周围的规则阻止工作更改。相反，您可以在 sysjobsteps 上放置一个 DML 触发器，以提醒您检查您的保留时间表，以确保新步骤不会从裂缝中溜走。如果一个步骤被删除并且您仍然有保留规则，这不是什么问题，但是如果您添加一个新步骤并且它的历史突然无限增长，这将是一个大问题。别担心；我们将创建一个安全网，用于在指定时间后清理工作，在某个时间点覆盖我们的规则。

接下来，如果我们想消除作业或步骤的任何上限（超过最大 1,000 行阈值），我们需要完全删除上限。如果您知道您只想将步骤限制为少于 1,000 行限制，您可以保留它，但如果您想为任何作业保留 1,000 行以上的历史记录，您可以通过以下调用删除此上限：

EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows = -1，@jobhistory_max_rows_per_job = -1;

msdb 数据库受到一些不幸的早期数据库设计决策的影响，这些决策在当前版本中一直存在。最值得注意的是使用整数在 sysjobhistory 表中分别存储日期和时间。所以，我创建了一个函数和视图来从我的任何查询中删除这些计算：

CREATE FUNCTION dbo.TVF_Agent_Datetime 
( 
  @date int, 
  @time int, 
  @duration int 
)
使用 SCHEMABINDING 
AS 
  RETURN 返回表
  ( 
    SELECT 
      StartDate, 
      EndDate = DATEADD(SECOND, DurationInSeconds, StartDate), 
      DurationInSeconds 
    FROM 
    ( 
        SELECT 
            StartDate(datetime,   
            TVERT) (varchar(4), @date / 10000) 
          + CONVERT(varchar(2), RIGHT('0' + RTRIM(@date % 10000 / 100),2)) 
          + CONVERT(varchar(2), RIGHT('0) ' + RTRIM(@date % 100),2)) 
          + ' ' + CONVERT(varchar(2), RIGHT('0' + RTRIM(@time / 10000),2))
          + ':' + CONVERT(varchar(2), RIGHT('0' + RTRIM(@time % 10000 / 100),2)) 
          + ':' + CONVERT(varchar(2), RIGHT('0' + RTRIM) (@time % 100),2))), 
        DurationInSeconds = 
            @duration / 1000000 * 24 * 60 * 60 
          + @duration / 10000 % 100 * 60 * 60 
          + @duration / 100 % 100 * 60 
          + 1 @0uration 
    )作为 x 
  ); 
GO 

CREATE VIEW dbo.JobStepHistory 
AS 
  SELECT 
    h.instance_id, h.job_id, h.step_id, h.run_status, 
    f.StartDate, f.EndDate, f.DurationInSeconds 
  FROM dbo.sysjobhistory AS h 
  CROSS APPLY 
    dbo.TVF_Agent_Datetime(h.run) , h.run_time, h.run_duration) AS f;

有了这个功能，我只需要通过几个步骤创建一个作业，并将保留规则插入到我的表中，就可以开始测试我将用来实现这些规则的逻辑。计划每分钟运行一次的作业分为三个步骤：一个成功，一个运行时间超过 30 秒，一个失败：

作业执行后，我们可以看看数据在我创建的视图中是如何呈现的：

SELECT TOP (4) * 
  FROM dbo.JobStepHistory
    ORDER BY instance_id DESC;

然后在本机 sysjobhistory 表中：

SELECT instance_id, step_id, step_name, sql_message_id, run_status, message 
  FROM dbo.sysjobhistory 
  WHERE instance_id IN (3393,3394,3395,3396)
    ORDER BY instance_id DESC;

现在保留规则开始发挥作用，因为我想确保我捕获并保留第 2 步运行时间超过 30 秒的任何时间，或者任何第 3 步失败的时间，但我不需要维护有关第 1 步的所有时间的数据成功。因此，我会将这些行放入我的 RetentionRules 表中：

DECLARE @job_id uniqueidentifier;

SELECT @job_id = job_id 
  FROM dbo.sysjobs 
  WHERE name LIKE N'Job A%';

INSERT dbo.RetentionRules
(
  job_id, 
  step_id, 
  NumberOfRowsToKeep, 
  NumberOfHoursToKeep,
  PreferRowsOrHours,
  KeepStepsThatFailed,
  DurationOverrideSeconds
)
VALUES
  (@job_id, 0, 100, NULL, 'R', 1, 180), -- keep 100 copies of job outcome
  (@job_id, 1, 15,  NULL, 'R', 1, 90),  -- keep 15 rows for step 1, unless fail / > 90 seconds
  (@job_id, 2, 80,  72,   'H', 1, 30),  -- keep 80 rows / 72 hours for step 2
                                        -- unless fail / > 30 seconds
  (@job_id, 3, 75,  NULL, 'R', 1, 90);  -- keep 75 rows for step 3, unless fail / > 90 seconds

现在我们只需要一个简单的查询来将我们的规则应用到历史记录中，删除那些只是噪音的行，并根据我们定义的首选项保留我们想要的行。随着时间的推移，我们应该看到我们总是丢弃第 1 步的历史记录，而我们保留第 2 步和第 3 步的历史记录。尽管如此。我强烈建议强制执行绝对年龄截止，这样您就不会永远保留失败或长期运行的步骤的历史记录。在这种情况下，我将其定义为 1,000 小时，但您可以根据您的环境选择适合您的时间。

DECLARE @FallBackAgeInHours int = 1000;

;WITH AllStepZeroes AS
(
  -- all instances of step zero (outcome)
  -- since no relational way to connect instance/steps
  -- LEAD() requires 2012+

  SELECT instance_id, job_id, StartDate, 
    NextStart = LEAD(startDate, 1) OVER (PARTITION BY job_id ORDER BY StartDate)
  FROM dbo.JobStepHistory
  WHERE step_id = 0
),

JoinHistoryToRules AS 
(
  -- find all the steps and match them to their retention rules

  SELECT h.*, 
    RowsToKeep = r.NumberOfRowsToKeep, 
    HoursToKeep = r.NumberOfHoursToKeep, 
    r.PreferRowsOrHours, 
    r.KeepStepsThatFailed, 
    r.DurationOverrideSeconds,
    -- simple counter to track number of instances of any job/step combo:
    rn = ROW_NUMBER() OVER (PARTITION BY h.job_id, h.step_id ORDER BY h.StartDate DESC)
  FROM dbo.JobStepHistory AS h 
  INNER JOIN dbo.RetentionRules AS r 
    ON h.job_id = r.job_id
    AND h.step_id = r.step_id
),

Step1 AS
(
  SELECT *,

  -- determine if we should keep or discard a row:

    -- if we have more row_number()s than the number we want to keep:

    DiscardDueToNumber = CASE 
      WHEN rn > RowsToKeep 
      THEN 1 ELSE 0 END,  

    -- if we have rows older than our age threshold:

    DiscardDueToAge = CASE 
      WHEN EndDate < DATEADD(HOUR, -HoursToKeep, GETDATE()) 
      THEN 1 ELSE 0 END,  

    -- if we want to keep steps that failed:

    KeepDueToFailure = CASE 
      WHEN step_id > 0 
       AND run_status = 0 – failed
       AND KeepStepsThatFailed = 1 
       THEN 1 ELSE 0 END,

    -- if we want to keep steps that ran longer than a defined threshold:

    KeepDueToDuration = CASE 
      WHEN DurationInSeconds > DurationOverrideSeconds 
      THEN 1 ELSE 0 END  

  FROM JoinHistoryToRules
), 

Step2 AS
(
  SELECT DeleteMe = CASE
    WHEN 
    (

      -- discard due to age unless we care about number of rows
      -- or vice versa. Captures case where both are true, too.

      (DiscardDueToNumber = 1 AND PreferRowsOrHours = 'R')
      OR
      (DiscardDueToAge = 1 AND PreferRowsOrHours = 'H')
    )
    AND 
    (
      -- if we are keeping due to failure or runtime
      -- let's not keep them forever

      (KeepDueToFailure = 0 AND KeepDueToDuration = 0)
      OR StartDate < DATEADD(HOUR, -@FallBackAgeInHours, GETDATE())
    )
    THEN 1 ELSE 0 END, *
    FROM Step1
),

Step3 AS
(
  SELECT *, 

    -- keep step 0 if we've kept any steps from that instance

    KeepStep0 = CASE WHEN step_id = 0 AND NOT EXISTS
    (
      SELECT 1 FROM AllStepZeroes AS w 
        WHERE w.job_id = Step2.job_id 
          AND Step2.step_id > 0 
          AND Step2.DeleteMe = 1
          AND Step2.StartDate >= w.StartDate 
          AND Step2.StartDate < w.NextStart
    ) THEN 1 ELSE 0 END
    FROM Step2
)

SELECT *. -- change to DELETE when happy:
  --DELETE h
  FROM dbo.sysjobhistory AS h
  INNER JOIN Step3 
    ON Step3.instance_id = h.instance_id
   AND Step3.DeleteMe = 1
   AND Step3.KeepStep0 = 0;

-- delete all other instances of jobs more than @FallBackAgeInHours old

;WITH LastFullInstanceBeforeCutoff AS 
(
  SELECT job_id, instance_id = MAX(instance_id) 
    FROM dbo.JobStepHistory
    WHERE step_id = 0
    AND EndDate < DATEADD(HOUR, -@FallBackAgeInHours, GETDATE())
    GROUP BY job_id
)
SELECT *
  --DELETE h  
  FROM dbo.sysjobhistory AS h
  INNER JOIN LastFullInstanceBeforeCutoff AS l
  ON h.job_id = l.job_id
  AND h.instance_id <= l.instance_id;

如果您很高兴这标识了您要从历史记录中删除的行，并忽略应该保留的行，请将两个 post-CTE SELECT 语句更改为 DELETE，并安排它按照您想要清理的频率运行历史表。如果您使用 SQL Server 代理安排此操作，请确保返回并将该作业添加到您的规则中，否则您将永远保留该历史记录。

未来的考虑 你可能会变得更复杂。

作业由于各种原因而失败，有时需要进一步排除故障，有时则不需要。由于 sysjobhistory 有 sql_message_id，指示错误消息，您可以保留一种类型的异常的历史记录，并为另一种类型丢弃它。也许您希望在周末更改历史记录的时间，在那里您的交易量较少，但希望在周一看到自周五以来发生的一切。因此，您可以添加有关如何处理星期五（保留 96 小时）、星期六（保留 72 小时）或星期日（保留 48 小时）的历史记录的条件规则。当然，您可以在查看和/或解决问题后轻松删除工作历史记录。这里有很多可能性；如果您有其他想要应用的规则，请在下方提及，我会尽我所能解决它们。

概括 使用本机设置维护作业历史可能相当有限。创建您自己的规则系统并应用它需要一点点思考，但从长远来看会非常有帮助，只保留您关心的历史记录并消除所有干扰。

---

原文链接：https://codingdict.com/

---