SQL Server：如何以人类可读的格式设置时间

剧本很糟糕，来源……不是很权威。：/

为了公平对待您的问题，监控 SQL Server 代理作业并不像人们想象的那么简单，尽管查询作者怀疑人类可读时间。

• SQL Server 代理作业表

• [dbo].[sysjobactivity]

  记录当前的 SQL Server 代理作业活动和状态。

  dbo.sysjobactivity | 微软文档

• [dbo].[sysjobhistory]

  包含有关 SQL Server 代理执行计划作业的信息。

  注意：数据仅在作业步骤完成后更新

  dbo.sysjob历史 | 微软文档

• [dbo].[系统作业]

  存储要由 SQL Server 代理执行的每个计划作业的信息。

  dbo.sysjobs | 微软文档

• [dbo].[sysjobschedules]

  包含要由 SQL Server 代理执行的作业的计划信息。

  注意：sysjobschedules 表每 20 分钟刷新一次，这可能会影响 sp_help_jobschedule 存储过程返回的值。

  dbo.sysjobschedules | 微软文档

• [dbo].[sysjobservers]

  存储特定作业与一个或多个目标服务器的关联或关系

  dbo.sysjobservers | 微软文档

• [dbo].[sysjobsteps]

  包含 SQL Server 代理要执行的作业中每个步骤的信息

  dbo.sysjobsteps | 微软文档

• [dbo].[sysjobstepslogs]

  包含配置为将作业步骤输出写入表的所有 SQL Server 代理作业步骤的作业步骤日志。

  dbo.sysjobstepslogs | 微软文档

• 如何找到失败的工作

由于目标是服务器的健康，因此请尝试将重点放在实际失败的作业上。关注平均值很好，但不是很有帮助，除非您是应用程序所有者并且您需要了解这些微小的细节。但即便如此，也要质疑请求以找出原因。

我偶然发现了一个出色的脚本，它可以让我找到每个失败的作业、失败的步骤以及在我们 200 多台服务器上报告的任何日志/消息。

USE msdb
GO
/* Create #Temp table to house job events */
IF EXISTS (SELECT 1 FROM tempdb.sys.tables WHERE NAme LIKE '#FailedJobs%')
    DROP TABLE #FailedJobs
CREATE TABLE #FailedJobs (Job VARCHAR(250)
                        , StepFailed VARCHAR(100)
                        , DateRun VARCHAR(30)
                        , TimeRun VARCHAR(30)
                        , Step_name VARCHAR(100)
                        , run_Duration INT
                        , LogOutput VARCHAR(MAX) )

DECLARE @JobName VARCHAR(250)
DECLARE @job_id UNIQUEIDENTIFIER

DECLARE JobsCursor CURSOR FORWARD_ONLY
FOR SELECT Name 
    FROM sysjobs A 
    INNER JOIN dbo.sysjobschedules B ON A.job_id = B.job_id
    WHERE A.enabled = 1
        AND B.next_run_date > 0

    OPEN JobsCursor
    FETCH NEXT FROM JobsCursor INTO @JobName
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @job_id = job_id FROM dbo.sysjobs WHERE [name] = @JobName        
    INSERT INTO #FailedJobs (Job, StepFailed, DateRun, TimeRun, Step_name, run_Duration, LogOutput )

    SELECT @JobName AS Job
        , 'Step ' + CAST(JH.step_id AS VARCHAR(3)) 
            + ' of ' + (    SELECT CAST(COUNT(*) AS VARCHAR(5)) 
                        FROM dbo.sysjobsteps 
                        WHERE job_id = @job_id
                        ) AS StepFailed
        , CAST(RIGHT(JH.run_date,2) AS CHAR(2)) 
            + '/' + CAST(SUBSTRING(CAST(JH.run_date AS CHAR(8)),5,2) AS CHAR(2)) 
            + '/' + CAST(LEFT(JH.run_date,4) AS CHAR(4)) AS DateRun
        , LEFT(RIGHT('0' + CAST(JH.run_time AS VARCHAR(6)),6),2) 
            + ':' + SUBSTRING(RIGHT('0' + CAST(JH.run_time AS VARCHAR(6)),6),3,2) 
            + ':' + LEFT(RIGHT('0' + CAST(JH.run_time AS VARCHAR(6)),6),2) AS TimeRun
        , JS.step_name 
        , JH.run_duration 
        , CASE WHEN JSL.[log] IS NULL THEN JH.[Message]
               ELSE JSL.[log]
          END AS LogOutput
    FROM dbo.sysjobsteps JS 
    INNER JOIN dbo.sysjobhistory JH ON JS.job_id = JH.job_id 
                                   AND JS.step_id = JH.step_id 
    LEFT OUTER JOIN dbo.sysjobstepslogs JSL ON JS.step_uid = JSL.step_uid
    WHERE INSTANCE_ID > (SELECT MIN(INSTANCE_ID)
                         FROM (
                                SELECT top (2) INSTANCE_ID, job_id
                                FROM dbo.sysjobhistory
                                WHERE job_id = @job_id
                                AND STEP_ID = 0
                                ORDER BY INSTANCE_ID desc
                                ) A
                        ) 
        AND JS.step_id <> 0 
        AND JH.job_id = @job_id
        /*Status of the job execution:
            0 = Failed
            1 = Succeeded
            2 = Retry
            3 = Canceled
        https://learn.microsoft.com/en-us/sql/relational-databases/system-tables/dbo-sysjobhistory-transact-sql
        */
        AND JH.run_status = 0
    ORDER BY JS.step_id ASC

    FETCH NEXT FROM JobsCursor INTO @JobName
END
CLOSE JobsCursor
DEALLOCATE JobsCursor

SELECT * 
FROM #FailedJobs

返回的格式非常有见地：

请注意此输出的可读性。您不仅可以看到哪个作业失败了，还可以看到失败的 step_id！LogOutput 将返回错误，所有这些都是可操作的。

因为您以后可能会需要这个，所以我添加了另外两个有用的查询：

1. 查找错误日志位置 - 特别是当上述查询被截断时

USE master
GO
EXEC xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, NULL, N'asc' 

https://www.mssqltips.com/sqlservertip/2506/identify-location-of-the-sql-server-error-log-file/

2. 从 sys.dm_exec_requests 和 sys.dm_exec_sessions 中的 Program_Name 解析 Job

曾经想查看作业正在运行的会话及其请求吗？那么现在你可以通过一些脚本轻松地检查这个

- 预解决方案

SELECT convert(varbinary(32), substring([Program_name], 30, 34), 1) AS Job_ID
     , sjob.name
     , s.*
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
INNER JOIN msdb.dbo.sysjobs sjob 
    ON convert(varbinary(32), substring([Program_name], 30, 34), 1) = sjob.job_id
WHERE program_name like 'SQLAgent - TSQL JobStep %';

只需解析 Program_Name 列以检索 Job_ID，并确保将其转换为 VARBINARY 形式。另请注意，您需要将转换类型指定为 1！

- 后解决方案

希望这能让您走得更远，并专注于可操作的信息。

它花了 1 年，但我有它。

我遵循了这个Microsoft 指南，我能够得到我想要的东西：我现在可以按日期、时间甚至持续时间过滤作业。

SELECT
sJobHis.[server],
sJobStep.database_name,
SJob.name,
SJob.enabled,
--CASE WHEN SJob.enabled = 0 THEN '0'
--WHEN sJobStep.subsystem = 'TSQL' AND sJobStep.command LIKE '%--%' 
--AND AVG(CAST(SUBSTRING(STUFF(STUFF(RIGHT('000000' + CAST([sJobHis].[run_duration] AS VARCHAR(6)),  6), 3, 0, ':'), 6, 0, ':') ,7,2) AS INT)) < 1 THEN '0'
--WHEN sJobStep.subsystem = 'TSQL' AND sJobStep.command LIKE '%*/%'
--AND AVG(CAST(SUBSTRING(STUFF(STUFF(RIGHT('000000' + CAST([sJobHis].[run_duration] AS VARCHAR(6)),  6), 3, 0, ':'), 6, 0, ':') ,7,2) AS INT)) < 1 THEN '0' ELSE '1' END AS  [ActiveStep],
sLogin.name [JobOwner],
sJobHis.step_id,
sJobHis.step_name,
sJobStep.subsystem AS [CommandType],
sJobStep.command AS [Command],
sJobHis.run_date,
--[sJobSch].next_run_time AS [Scheduled_Time],
STUFF(STUFF(RIGHT(REPLICATE('0', 6) +  CAST([sJobSch].next_run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') 'Scheduled_Time',
--sJobHis.run_time,
--sJobHis.run_duration,
STUFF(STUFF(RIGHT(REPLICATE('0', 6) +  CAST(sJobHis.run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') 'run_time',
STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(sJobHis.run_duration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') 'run_duration (DD:HH:MM:SS)',
--AVG( CAST( SUBSTRING(STUFF(STUFF(RIGHT('000000' + CAST([sJobHis].[run_duration] AS VARCHAR(6)),  6), 3, 0, ':'), 6, 0, ':') ,4,2)AS INT)) AS [AvgRunDuration_In_Min], 
--AVG(CAST(SUBSTRING(STUFF(STUFF(RIGHT('000000' + CAST([sJobHis].[run_duration] AS VARCHAR(6)),  6), 3, 0, ':'), 6, 0, ':') ,7,2) AS INT)) AS [AvgRunDuration_In_Sec],
COUNT(*) AS [PerDay]

FROM msdb.dbo.sysjobs AS [SJob] 
LEFT JOIN msdb.dbo.sysjobhistory AS [sJobHis] ON SJob.job_id = sJobHis.job_id
LEFT JOIN msdb.dbo.sysjobsteps AS [sJobStep] ON sJobHis.job_id = sJobStep.job_id AND sJobHis.step_id = sJobStep.step_id
INNER JOIN master.dbo.syslogins [sLogin] ON SJob.owner_sid = sLogin.sid
INNER JOIN msdb.dbo.sysjobschedules [sJobSch] ON SJob.job_id = [sJobSch].job_id
WHERE  SJob.[enabled]=0 OR ( sJobHis.step_id > 0  AND (sJobHis.run_date > 20180825 AND sJobHis.run_date < 20180829)) -- PUT HERE YOUR DATES
GROUP BY sJobHis.[server],
sJobStep.database_name,
SJob.name,
SJob.enabled,
sLogin.name ,
sJobHis.step_id,
sJobHis.step_name,
sJobStep.subsystem ,
sJobStep.command ,
sJobHis.run_date,
sJobHis.run_time,
sJobHis.run_duration,
[sJobSch].next_run_time
ORDER BY SJob.enabled DESC, SJob.name, sJobHis.run_date DESC

我保留了古老的行，这样你就可以看到我所做的改变。