SQL Server - 获取作业历史记录详细信息

SQL Server - Getting job history details

提问by Conrad Jagger

We use the below mentioned Stored Procedure to get the history of our JOBS

我们使用下面提到的存储过程来获取我们的 JOBS 历史

SELECT --sysjobhistory.server,
             sysjobs.name
             AS
             job_name,
             CASE sysjobhistory.run_status
               WHEN 0 THEN 'Failed'
               WHEN 1 THEN 'Succeeded'
               ELSE '???'
             END
             AS
             run_status,
             CAST(
             Isnull(Substring(CONVERT(VARCHAR(8), run_date), 1, 4) + '-' +
                           Substring(CONVERT(VARCHAR
                                     (8), run_date), 5, 2) + '-' +
                    Substring(CONVERT(VARCHAR(
                              8), run_date), 7, 2), '') AS DATETIME)
             AS
             [Run DATE],

             Isnull(Substring(CONVERT(VARCHAR(7), run_time+1000000), 2, 2) + ':'
                     +
                           Substring(CONVERT(VARCHAR(7), run_time+1000000), 4, 2
                            )
                    +
                    ':' +
                    Substring(CONVERT(VARCHAR(7), run_time+1000000), 6, 2), '') 
             AS
             [Run TIME],
             Isnull(Substring(CONVERT(VARCHAR(7), run_duration+1000000), 2, 2) +
                     ':' +
                           Substring(CONVERT(VARCHAR(7), run_duration+1000000),
                           4,
                           2)
                    + ':' +
                    Substring(CONVERT(VARCHAR(7), run_duration+1000000), 6, 2),
             ''
             ) AS
             [Duration],
             Isnull(Substring(CONVERT(VARCHAR(7), run_time+run_duration+1000000), 2, 2) + ':'
                     +
                           Substring(CONVERT(VARCHAR(7), run_time+run_duration+1000000), 4, 2
                            )
                    +
                    ':' +
                    Substring(CONVERT(VARCHAR(7), run_time+run_duration+1000000), 6, 2), '')
             AS
             [Total TIME],             
             sysjobhistory.step_id,
             sysjobhistory.step_name,
             sysjobhistory.MESSAGE AS Msg
      FROM   msdb.dbo.sysjobhistory
             INNER JOIN msdb.dbo.sysjobs
               ON msdb.dbo.sysjobhistory.job_id = msdb.dbo.sysjobs.job_id
      WHERE  sysjobhistory.run_date <= Datepart(yyyy, @dateparam) * 10000 +
                                             Datepart(mm, @dateparam) * 100 +
                                      Datepart
                                      (
                                             dd, @dateparam)
             AND sysjobs.name = @JobName --remove this line if you want to show all jobs for the specified day

---

---

But can anyone advise how to get error details if the job has failed for example (we are looking for this information which we can view thru log viewer)

但是，如果作业失败，任何人都可以建议如何获取错误详细信息（我们正在寻找可以通过日志查看器查看的信息）

Date        29/09/2011 07:57:04
Log     Job History Step ID     5
Server       
Job Name        
Step Name       extract
Duration        00:02:13
Sql Severity        0
Sql Message ID      0
Operator Emailed        
Operator Net sent       
Operator Paged      
Retries Attempted       0

Message (Need this error message also to come)

消息（也需要此错误消息）

回答by w2bsqldba

you can use the following script to grab the details of each step..first you have to get the failed job's job_id and pass to msdb.dbo.sp_help_jobhistory stored procedure.

您可以使用以下脚本获取每个步骤的详细信息。首先，您必须获取失败作业的 job_id 并传递给 msdb.dbo.sp_help_jobhistory 存储过程。

I got this script by running trace while doing the activity on job activity monitor.

我通过在作业活动监视器上执行活动时运行跟踪来获得此脚本。

I hope this helps you on what you are looking for

我希望这可以帮助你找到你正在寻找的东西

To find STEP level messages, look at the messagecolumn where step_id<> 0

要查找 STEP 级别的消息，请查看<> 0message所在的列step_id

These records actually provide useful error messages.

这些记录实际上提供了有用的错误消息。

declare @tmp_sp_help_jobhistory table
(
    instance_id int null, 
    job_id uniqueidentifier null, 
    job_name sysname null, 
    step_id int null, 
    step_name sysname null, 
    sql_message_id int null, 
    sql_severity int null, 
    message nvarchar(4000) null, 
    run_status int null, 
    run_date int null, 
    run_time int null, 
    run_duration int null, 
    operator_emailed sysname null, 
    operator_netsent sysname null, 
    operator_paged sysname null, 
    retries_attempted int null, 
    server sysname null  
)

insert into @tmp_sp_help_jobhistory 
exec msdb.dbo.sp_help_jobhistory 
    @job_id = '329cac18-328d-499b-9216-593244d164b0', --change this job_id according to your requirement
    @mode='FULL' 

SELECT
    tshj.instance_id AS [InstanceID],
    tshj.sql_message_id AS [SqlMessageID],
    tshj.message AS [Message],
    tshj.step_id AS [StepID],
    tshj.step_name AS [StepName],
    tshj.sql_severity AS [SqlSeverity],
    tshj.job_id AS [JobID],
    tshj.job_name AS [JobName],
    tshj.run_status AS [RunStatus],
    CASE tshj.run_date WHEN 0 THEN NULL ELSE
    convert(datetime, 
            stuff(stuff(cast(tshj.run_date as nchar(8)), 7, 0, '-'), 5, 0, '-') + N' ' + 
            stuff(stuff(substring(cast(1000000 + tshj.run_time as nchar(7)), 2, 6), 5, 0, ':'), 3, 0, ':'), 
            120) END AS [RunDate],
    tshj.run_duration AS [RunDuration],
    tshj.operator_emailed AS [OperatorEmailed],
    tshj.operator_netsent AS [OperatorNetsent],
    tshj.operator_paged AS [OperatorPaged],
    tshj.retries_attempted AS [RetriesAttempted],
    tshj.server AS [Server],
    getdate() as [CurrentDate]
FROM @tmp_sp_help_jobhistory as tshj
ORDER BY [InstanceID] ASC