主页 / dba / 问题 / 187672
Accepted
SQLDoug
Asked: 2017-10-05 08:24:18 +0800 CST

在 sysjobhistory 中将单个运行的作业步骤链接在一起

  • 772

我们有几个 SQL Server 运行 SQL 代理作业，在它们自己的 sysjobhistory 表中记录历史。我正在尝试设置一个集中式服务器，其作业收集所有其他服务器的历史记录，对其进行格式化，然后将其放入名为 AllJobHistory 的表中。作为此过程的一部分，我希望在一个列中表明作业的多个步骤是同一作业运行的一部分。它们已通过 job_id 列标记为同一作业的一部分，但我想知道特定行来自作业的 3:00 运行与 4:00 运行。能够基于此列进行过滤将使我们的故障排除工作变得更加容易，但我看不到任何现有系统表或 DMV 中将这些步骤链接在一起的任何内容，是吗？

我自己的第一次尝试是使用 run_date、run_time 和 run_duration 列。对于每一步，如果我从 run_time 中减去到目前为止的总 run_duration，它应该让我回到与该作业的所有其他运行相比独一无二的时间。看起来它一直在工作，直到我发现它不是（可能是因为 SQL Server 以秒为精度四舍五入 run_time 和 run_duration）。这是我对查询的尝试（删除了额外的列）。

WITH JobDetails AS 
(
    SELECT 
        QUOTENAME(UPPER('ServerName')) AS [Server],
        j.job_id AS [JobID],
        j.name AS [JobName],
        s.step_id AS [Step],
        msdb.dbo.agent_datetime(run_date, run_time) AS [RunDate],
        (run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100) AS [RunDurationSeconds]
    FROM msdb.dbo.sysjobhistory h
    INNER JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id
    LEFT OUTER JOIN msdb.dbo.sysjobsteps s ON s.job_id = h.job_id AND s.step_id = h.step_id 
    WHERE h.step_id != 0
), GroupedDetails AS (
    SELECT 
        jd.[Server],
        jd.[JobID],
        jd.JobName,
        jd.Step,
        jd.RunDate,
        jd.RunDurationSeconds,
        DATEADD(SECOND, 
            -ISNULL(SUM(jd.RunDurationSeconds) OVER
                (PARTITION BY jd.JobName ORDER BY jd.JobName, jd.RunDate, jd.Step 
                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0), 
            jd.RunDate) AS grp
    FROM JobDetails AS  jd
)
SELECT 
    gd.[Server],
    gd.JobName,
    gd.Step,
    gd.RunDate,
    gd.RunDurationSeconds,
    CONVERT(VARCHAR(36), gd.JobID) + '_' + FORMAT(gd.grp, 'yyyyMMdd_HHmmss') AS JobRunString
FROM GroupedDetails AS gd;

这是一个示例，它按我的意愿工作，对于一个包含三个步骤的工作。请注意，JobRunString 匹配第一次运行和第二次运行。 工作示例

这是一个示例，它没有按我的意愿工作。注意 Step1RunDate + Step1RunDurationSeconds != Step2RunDate，导致 JobRunString 不匹配。 不工作示例

那么，是否有任何可靠的方法可以将 sysjobhistory 中运行的作业步骤链接在一起？

sql-server

4 个回答

  1. Best Answer

    请注意，它sysjobhistory有一个 ID 列 ( instance_id)。对于已完成的作业的每个步骤，至少应该有一个条目，然后是一个step_id= 0 的条目，记录作业的结果。每个步骤还记录步骤开始的时间 (run_date和run_time)，这将等于或大于作业开始的时间。因此，step_id给定运行的 = 0 行的运行时间高于 instance_id相关步骤，但运行时间较短（或相等）。

    因此，请尝试将 = 0 的行中的数据初始提取step_id到临时表（或等效表）中。sysjobhistory然后，具有相同job_id、较低 instance_id和较高或相等开始时间（从run_date和）的所有行都run_time应该属于您正在寻找的作业运行。

    我曾经在前雇主的一份失败的工作报告中使用过类似的东西。

    这是该代码的精简修改版本。刚才我在 SQL Server 2016 机器上进行了快速测试。但是，我没有任何运行频率足够高以至于多次运行具有相同运行时间的作业。

    USE msdb;

DECLARE @start_date varchar(8) = '20171001';

IF(OBJECT_ID('tempdb..#failhist') IS NOT NULL) DROP TABLE #failhist;

CREATE TABLE #failhist
(
    [job_name] [sysname] NOT NULL,
    [run_datetime] [datetime] NULL,
    [run_ended] [datetime] NULL,
    [instance_id] [int] NOT NULL,
    [job_id] [uniqueidentifier] NOT NULL,
    [run_date] [int] NOT NULL,
    [run_time] [int] NOT NULL
);


INSERT INTO #failhist
select *
  FROM
(SELECT j.name as job_name
       ,CONVERT(DATETIME, RTRIM(run_date)) + (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4
        as run_datetime
       ,CONVERT(DATETIME, RTRIM(run_date)) + (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4 + (run_duration * 9 + run_duration % 10000 * 6 + run_duration % 100 * 10) / 216e4
        as run_ended
       ,h.instance_id
       ,h.job_id
       ,h.run_date
       ,h.run_time
   from msdb..sysjobhistory h INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id
  WHERE h.step_id = 0
    and h.run_date >= @start_date
) x
;

SELECT
       t.job_name AS [Job/Step]
      ,' ' + CONVERT(varchar(19), t.run_datetime, 121) + ' to ' + CONVERT(varchar(19), t.run_ended, 121) AS Run_Msg
      ,0 as HdrDetail
      ,t.job_name
      ,t.run_datetime
      ,t.instance_id
      ,-1 as step_id
      ,jh.instance_id as actual_instance_id
  FROM #failhist t
         INNER JOIN msdb..sysjobhistory jh ON (    t.job_id = jh.job_id
                                               AND t.instance_id >= jh.instance_id
                                               AND (   t.run_date < jh.run_date
                                                    OR (    t.run_date = jh.run_date
                                                        AND t.run_time <= jh.run_time
                                                       )
                                                   )
                                              )
 WHERE jh.step_id = 0
UNION ALL
SELECT
       '        '
      +CASE
         WHEN jh.step_id = 0
           THEN 'Job Summary'
         WHEN jh.run_status BETWEEN 0 AND 1  -- summary
           THEN RIGHT(CAST(100 + jh.step_id as varchar),2) + ' - ' + jh.step_name
         ELSE   '  ' + RIGHT(CAST(100 + jh.step_id as varchar),2) + ' (add''l info)'
       END
      ,'        ' + CAST(jh.message as varchar(max))
      ,1
      ,t.job_name
      ,t.run_datetime
      ,t.instance_id
      ,jh.step_id
      ,jh.instance_id
  FROM #failhist t
         INNER JOIN msdb..sysjobhistory jh ON (    t.job_id = jh.job_id
                                               AND t.instance_id >= jh.instance_id
                                               AND (   t.run_date < jh.run_date
                                                    OR (    t.run_date = jh.run_date
                                                        AND t.run_time <= jh.run_time
                                                       )
                                                   )
                                              )
 ORDER BY run_datetime, instance_id, step_id

    如果您实际上有一个每秒运行一次以上的作业，您可能必须使用窗口函数，以确保您不会从具有相同 run_time 值的作业的早期运行中选择作业步骤。

    警告：如果达到每个作业可以包含的行数限制sysjobhistory，您可能会得到奇怪/不完整的结果。此外，我偶尔会看到作业失败而没有生成作业结果（通常是暂时无法对运行作业的 Windows 用户进行身份验证）。

    • 4

  2. 感谢@RDFozz 提供的信息，我能够提出一个查询来获取我正在寻找的所有数据。我不想包含第 0 步“工作结果”行，并且我还删除了 @RDFozz 查询中的漂亮格式。这对于报告来说很棒，但我希望它更像是一个关系表。

    IF OBJECT_ID('tempdb..#JobHist') IS NOT NULL DROP TABLE #JobHist;

CREATE TABLE #JobHist
(
    [job_name] [sysname] NOT NULL,
    [run_datetime] [datetime] NULL,
    [instance_id] [int] NOT NULL,
    [job_id] [uniqueidentifier] NOT NULL,
    [run_date] [int] NOT NULL,
    [run_time] [int] NOT NULL,
);

--Datetime calculation explanation: https://www.sqlservercentral.com/Forums/Topic542581-145-1.aspx
INSERT INTO #JobHist
SELECT
    j.name as job_name
    ,DATEADD(SECOND, (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 25.0, CONVERT(DATETIME2(0),RTRIM(run_date))) AS run_datetime
    ,h.instance_id
    ,h.job_id
    ,h.run_date
    ,h.run_time
FROM msdb.dbo.sysjobhistory h 
INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
WHERE h.step_id = 0;

SELECT
    QUOTENAME(UPPER('ServerName')) AS [Server],
    t.job_name AS JobName,
    jh.step_id AS StepID,
    jh.step_name AS StepName,
    CASE jh.run_status 
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Successful'
        WHEN 2 THEN 'Retry - step only'
        WHEN 3 THEN 'Cancelled'
        WHEN 4 THEN 'In Progress'
        ELSE 'Unknown'
    END AS RunStatus,
    CONVERT(VARCHAR(MAX), jh.[message]) AS [Message],
    js.[database_name] AS [Database],
    js.Command,
    DATEADD(SECOND, (jh.run_time * 9 + jh.run_time % 10000 * 6 + jh.run_time % 100 * 10) / 25.0, CONVERT(DATETIME2(0), RTRIM(jh.run_date))) AS RunDate,
    (jh.run_duration/10000*3600 + (jh.run_duration/100)%100*60 + jh.run_duration%100) AS [RunDurationSeconds],
    jh.job_id AS JobID,
    t.instance_id AS LocalRunID
INTO #JobRunDetails
FROM #JobHist t
INNER JOIN msdb.dbo.sysjobhistory jh 
    ON t.job_id = jh.job_id
        AND t.instance_id >= jh.instance_id
        AND t.run_date <= jh.run_date
        AND t.run_time <= jh.run_time
INNER JOIN msdb.dbo.sysjobsteps js ON js.job_id = jh.job_id AND js.step_id = jh.step_id
WHERE jh.step_id != 0;

    为了将许多服务器的结果编译到一个服务器上，然后我执行以下操作以获得每个作业运行的数字，该数字在服务器之间是唯一的（dbo.DBA_AllJobHistory_JobRun 是一个Sequence）：

    SELECT DISTINCT 
    LocalRunID,
    CAST(NULL AS BIGINT) AS RunID
INTO #JobRunSequence
FROM #JobRunDetails;

UPDATE #JobRunSequence SET RunID = NEXT VALUE FOR dbo.DBA_AllJobHistory_JobRun;

    然后这会将来自该服务器的历史记录插入到组合多个服务器的 AllJobHistory 表中。

        MERGE dbo.DBA_AllJobHistory as Target
    USING (
        SELECT
            d.Server,
            d.JobID,
            d.StepID,
            s.RunID,
            d.JobName,
            d.StepName,
            d.RunStatus,
            d.Message,
            d.[Database],
            d.Command,
            d.RunDate,
            d.RunDurationSeconds
        FROM #JobRunDetails d
        INNER JOIN #JobRunSequence s ON s.LocalRunID = d.LocalRunID
    ) AS Source
    ON (Target.Server   = Source.Server AND
        Target.JobID    = Source.JobID  AND
        Target.StepID   = Source.StepID AND
        Target.RunDate  = Source.RunDate)
    WHEN MATCHED THEN 
        UPDATE SET  Target.RunStatus            = Source.RunStatus,     
                    Target.Message              = Source.Message,           
                    Target.RunDurationSeconds   = Source.RunDurationSeconds
    WHEN NOT MATCHED THEN 
        INSERT (Server,
                JobID,
                StepID,
                RunID,
                JobName,
                StepName,
                RunStatus,
                Message,
                [Database],
                Command,
                RunDate,
                RunDurationSeconds
                )
        VALUES (Source.Server,
                Source.JobID,
                Source.StepID,
                Source.RunID,
                Source.JobName,
                Source.StepName,
                Source.RunStatus,
                Source.Message,
                Source.[Database],
                Source.Command,
                Source.RunDate,
                Source.RunDurationSeconds
                );
    • 2

  3. 这是我的解决方案：（我的解决方案还提取了 execution_id: 用于从 SQL Server 作业执行的 SSIS。我在其他地方得到了部分查询，我忘记了在哪里）。

    SELECT  sjhP.job_id, job.[name] JobName, 
        sjhP.instance_id parent_instance_id, 

        sjhC.instance_id, sjhC.step_id, sjhC.step_name, sjhC.run_status, 
        msdb.dbo.agent_datetime(sjhC.run_date, sjhC.run_time) 
        DATEADD(SECOND, 
                    ( ( sjhC.run_duration / 1000000 ) * 86400 )
                    + ( ( ( sjhC.run_duration - ( ( sjhC.run_duration / 1000000 ) * 1000000 ) ) / 10000 ) * 3600 )
                    + ( ( ( sjhC.run_duration - ( ( sjhC.run_duration / 10000 ) * 10000 ) ) / 100 ) * 60 ) + 
                          ( sjhC.run_duration - ( sjhC.run_duration / 100 ) * 100 ),
                    msdb.dbo.agent_datetime(sjhC.run_date, sjhC.run_time)) run_enddatetime, 
        --activity.run_requested_date, activity.start_execution_date, activity.stop_execution_date, activity.last_executed_step_date, activity.last_executed_step_id, 
        (sjhC.run_duration/10000 /*HRS*/)*3600 + ((sjhC.run_duration%10000)/100 /*Mins*/)*60 + ((sjhC.run_duration%10000)%100 /*Secs*/) run_duration_seconds, 
        SUBSTRING(sjhC.[message], NULLIF(CHARINDEX('Execution ID: ', sjhC.[message]),0)+14 ,PATINDEX('%[^0-9]%',SUBSTRING(sjhC.[message], NULLIF(CHARINDEX('Execution ID: ', sjhC.[message]),0)+14 ,20))-1) execution_id,                     
        sjhC.sql_message_id, sjhC.sql_severity, sjhC.[message] JobMessage, sjhC.run_date, sjhC.run_time, sjhC.run_duration, 
        sjhC.operator_id_emailed, sjhC.operator_id_netsent, sjhC.operator_id_paged, sjhC.retries_attempted, sjhC.[server] 
        --SELECT *
FROM (  SELECT  msdb.dbo.agent_datetime(sjh0.run_date, sjh0.run_time) run_datetime,
                (sjh0.run_duration/10000 /*HRS*/)*3600 + ((sjh0.run_duration%10000)/100 /*Mins*/)*60 + ((sjh0.run_duration%10000)%100 /*Secs*/) run_duration_seconds, 
                DATEADD(SECOND, 
                        ( ( sjh0.run_duration / 1000000 ) * 86400 )
                        + ( ( ( sjh0.run_duration - ( ( sjh0.run_duration / 1000000 ) * 1000000 ) ) / 10000 ) * 3600 )
                        + ( ( ( sjh0.run_duration - ( ( sjh0.run_duration / 10000 ) * 10000 ) ) / 100 ) * 60 ) + 
                              ( sjh0.run_duration - ( sjh0.run_duration / 100 ) * 100 ),
                        msdb.dbo.agent_datetime(sjh0.run_date, sjh0.run_time)) run_enddatetime, --from: http://sqldbpros.com/2013/08/sql-job-steps-and-run_duration-the-query-that-formats-run_duration-correctly-and-doesnt-make-you-want-to-shoot-your-eye-out/
                ( SELECT MAX(sjh0b.instance_id) prev_instance_id 
                  FROM MSDB.dbo.SysJobHistory sjh0b
                  WHERE sjh0b.job_id = sjh0.job_id AND
                        sjh0b.step_id = 0 AND 
                        msdb.dbo.agent_datetime(sjh0b.run_date, sjh0b.run_time) < msdb.dbo.agent_datetime(sjh0.run_date, sjh0.run_time) 
                ) prev_instance_id, 
                * 
        FROM MSDB.dbo.SysJobHistory sjh0 
        WHERE sjh0.step_id = 0   
     ) sjhP LEFT JOIN
     MSDB.dbo.SysJobHistory sjhC
  ON sjhC.job_id = sjhP.job_id AND
     sjhC.instance_id > ISNULL(sjhP.prev_instance_id,0) AND
     sjhC.instance_id <= sjhP.instance_id LEFT JOIN
     MSDB.DBO.SysJobs job
  ON job.job_id = sjhP.job_id /*LEFT JOIN
     msdb.dbo.SysJobActivity activity
  ON activity.job_id = sjhP.job_id*/
WHERE sjhP.step_id = 0

    我花了很多时间思考如何才能获得工作所需的内容，并尝试了不同的方法，例如使用 SysJobActivity 表来帮助我选择相关的步骤，但这不是一种可靠的方法。我想要每次跑步，而不仅仅是最近一次跑步。

    我最终注意到，对于每次运行，总是有一个 step_id = 0，即使它在运行 step_id = 1 之前失败，并且那个 step_id 是最后写入的。因此，当搜索 step_order = 0 的给定 job_id 时，您可以计算出在 step_order = 0 相同作业的上一次运行和当前作业的 step_order = 0 运行之间运行的所有作业步骤。

    我从其他人那里得到了一些想法，它们并不都是我自己的。但是现在我看到我在这个堆栈交换帖子中拥有的东西与其他人有一些相似之处。但我的也不一样。现在对我来说效果很好。

    我在这里的工作是主要的内部和外部联接以及将 prev_instance_id 与外部查询联接的方案，然后在最外部的查询中确定哪些子行与哪些父行一起使用 (step_id = 0)。

    在我重写查询之前我有一些代码，可能昨天完成了。我曾经拥有的那个代码是一个 hack，它不可靠并且存在问题。我希望 Microsoft 使 SQL Server 作业代理作业表更易于理解。他们应该提供 parent_instance_id 或类似的东西来链接它们或其他一些简单的机制。但我在上面的查询中创建了这个。您可以非常轻松地将其转换为视图或函数或存储过程，以使其灵活且可重用！我有一个视图，稍后会被存储过程调用！

    • 1

  4. 我使用上面的示例和 CTE 创建了一个查询来拉回所有存在的执行历史记录，并将步骤 0 Job_Outcome 作为每个作业步骤的列包含在内。这以一种易于存储的格式为我提供了历史记录。

                                WITH [Job_Outcome] ([job_id], [instance_id], [run_date], [run_time], [step_id], [run_status], [message])
                            AS (SELECT [J].[job_id],
                                       [h].[instance_id],
                                       [h].[run_date],
                                       [h].[run_time],
                                       [h].[step_id],
                                       [h].[run_status],
                                       [h].[message]
                                FROM [msdb].[dbo].[sysjobhistory] AS [h]
                                    INNER JOIN [msdb].[dbo].[sysjobs] AS [J]
                                        ON [h].[job_id] = [J].[job_id]
                                WHERE 1 = 1
                                      AND [msdb].[dbo].[agent_datetime]([h].[run_date], [h].[run_time]) > '1900-01-01 00:00:00.000'
                                      AND [step_id] = 0)
                            SELECT [Job_Outcome].[instance_id] AS "Job_ExecutionID",
                                   [j].[name] AS "JobName",
                                   [j].[description] AS "JobDescription",
                                   [j].[enabled],
                                   [h].[step_id] AS "StepID",
                                   [h].[step_name] AS "StepName",
                                   [msdb].[dbo].[agent_datetime]([h].[run_date], [h].[run_time]) AS "StartTime",
                                   [j].[start_step_id],
                                   [h].[run_status] AS "StepStatus",
                                   CASE ([h].[run_status])
                                       WHEN 0 THEN
                                           'Failed'
                                       WHEN 1 THEN
                                           'Succeeded'
                                       WHEN 2 THEN
                                           'Retry'
                                       WHEN 3 THEN
                                           'Canceled'
                                       WHEN 4 THEN
                                           'Running' -- In Progress
                                   END AS "StepStatusDesc",
                                   [Job_Outcome].[run_status] AS "JobStatus",
                                   CASE ([Job_Outcome].[run_status])
                                       WHEN 0 THEN
                                           'Failed'
                                       WHEN 1 THEN
                                           'Succeeded'
                                       WHEN 2 THEN
                                           'Retry'
                                       WHEN 3 THEN
                                           'Canceled'
                                       WHEN 4 THEN
                                           'Running' -- In Progress
                                   END AS "JobStatusDesc",
                                   [Job_Outcome].[message] AS "JobCompletionMessage",
                                   [h].[sql_message_id] AS "MessageID",
                                   [h].[message] AS "MessageLog",
                                   CASE LEN([h].[run_duration])
                                       WHEN 1 THEN
                                           CAST('00:00:0' + CAST([h].[run_duration] AS CHAR) AS CHAR(8))
                                       WHEN 2 THEN
                                           CAST('00:00:' + CAST([h].[run_duration] AS CHAR) AS CHAR(8))
                                       WHEN 3 THEN
                                           CAST('00:0' + LEFT(RIGHT([h].[run_duration], 3), 1) + ':' + RIGHT([h].[run_duration], 2) AS CHAR(8))
                                       WHEN 4 THEN
                                           CAST('00:' + LEFT(RIGHT([h].[run_duration], 4), 2) + ':' + RIGHT([h].[run_duration], 2) AS CHAR(8))
                                       WHEN 5 THEN
                                           CAST('0' + LEFT(RIGHT([h].[run_duration], 5), 1) + ':' + LEFT(RIGHT([h].[run_duration], 4), 2) + ':' + RIGHT([h].[run_duration], 2) AS CHAR(8))
                                       WHEN 6 THEN
                                           CAST(LEFT(RIGHT([h].[run_duration], 6), 2) + ':' + LEFT(RIGHT([h].[run_duration], 4), 2) + ':' + RIGHT([h].[run_duration], 2) AS CHAR(8))
                                   END AS "DurationHH:MM:SS",
                                   [s].[subsystem],
                                   [j].[version_number],
                                   [j].[date_created],
                                   [j].[date_modified]
                            FROM [msdb].[dbo].[sysjobhistory] AS [h]
                                INNER JOIN [msdb].[dbo].[sysjobs] AS [j]
                                    ON [h].[job_id] = [j].[job_id]
                                INNER JOIN [msdb].[dbo].[sysjobsteps] AS [s]
                                    ON [j].[job_id] = [s].[job_id]
                                       AND [h].[step_id] = [s].[step_id]
                                LEFT JOIN [Job_Outcome]
                                    ON (
                                           [j].[job_id] = [Job_Outcome].[job_id]
                                           AND [h].[instance_id] < [Job_Outcome].[instance_id]
                                           AND
                                               (
                                                   [h].[run_date] > [Job_Outcome].[run_date]
                                                   OR
                                                       (
                                                           [h].[run_date] = [Job_Outcome].[run_date]
                                                           AND [h].[run_time] >= [Job_Outcome].[run_time]
                                                       )
                                               )
                                       )
                            WHERE 1 = 1
                                  --AND [h].[run_status] NOT IN ( 1 )
                                  --AND [Job_Outcome].[run_status] NOT IN ( 1 )
                                  AND [msdb].[dbo].[agent_datetime]([h].[run_date], [h].[run_time]) > '1900-01-1 00:00:00.000'
                            ORDER BY [msdb].[dbo].[agent_datetime]([h].[run_date], [h].[run_time]) DESC;
    • 1

相关问题