时间戳

首页 > 解决方案 > 想要列出在 SQL Server 中手动执行的所有作业

sql-server - 想要列出在 SQL Server 中手动执行的所有作业

问题描述

如何获取上周手动执行的所有作业的列表？

标签： sql-serversql-job

解决方案

OP 询问如何告诉所有手动执行的作业。为此，Tomato 的查询应包括对引用“run_requested_source”列的 msdb.dbo.sysjobactivity 的连接。

来自微软。

run_requested_source（谁请求运行该作业）

1 = SOURCE_SCHEDULER
2 = SOURCE_ALERTER
3 = SOURCE_BOOT
4 = SOURCE_USER
6 = SOURCE_ON_IDLE_SCHEDULE

    SELECT sj.name,
       sjh.step_name,
       sjh.step_id,
       sjh.run_status,
       STUFF(STUFF(CAST(sjh.run_date as nvarchar(10)),5,0,'.'),8,0,'.') as HistRunDate,
       STUFF(STUFF(RIGHT(REPLICATE('0', 6) +  CAST(sjh.run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') as HistRunTime,
       STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(sjh.run_duration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') as HistRunDuration
       ,sjh.run_status AS JobStatus

FROM   msdb.dbo.sysjobs           AS sj
       join msdb.dbo.sysjobhistory AS sjh on sjh.job_id = sj.job_id
       join msdb.dbo.sysjobactivity AS sa on sj.job_id = sa.job_id
WHERE
    (
        ((sjh.run_date = 20190401 and sjh.run_time > 200000) 
        or (sjh.run_date = 20190409 and sjh.run_time < 100000))
    )
    and sa.run_requested_source = 4

ORDER BY
       sjh.run_date,
       sjh.run_time

推荐阅读