原 MSSQL作业JOB介绍
Tags: 原创MSSQLSQL Server小麦苗常用job作业
简介
SQL Server 作业是 SQL Server 数据库管理系统中的一种自动化任务调度工具,用于执行一系列预定义的任务或操作。作业可以包括诸如备份、数据清理、维护、ETL(抽取、转换和加载)过程等一系列操作,可以根据计划或事件来执行。
查询所有作业(用这个)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | SELECT sj.name AS [Name], sh.step_id, sh.step_name AS [StepName], DATETIMEFROMPARTS( LEFT(padded_run_date, 4), -- year SUBSTRING(padded_run_date, 5, 2), -- month RIGHT(padded_run_date, 2), -- day LEFT(padded_run_time, 2), -- hour SUBSTRING(padded_run_time, 3, 2), -- minute RIGHT(padded_run_time, 2), -- second 0) AS [LastRunDateTime], -- millisecond CASE WHEN sh.run_duration > 235959 THEN CAST((CAST(LEFT(CAST(sh.run_duration AS VARCHAR), LEN(CAST(sh.run_duration AS VARCHAR)) - 4) AS INT) / 24) AS VARCHAR) + '.' + RIGHT('00' + CAST(CAST(LEFT(CAST(sh.run_duration AS VARCHAR), LEN(CAST(sh.run_duration AS VARCHAR)) - 4) AS INT) % 24 AS VARCHAR), 2) + ':' + STUFF(CAST(RIGHT(CAST(sh.run_duration AS VARCHAR), 4) AS VARCHAR(6)), 3, 0, ':') ELSE STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(sh.run_duration AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') END AS [LastRunDuration (d.HH:MM:SS)], sh.run_status, sh.message, sh.server, jstep.command FROM msdb.dbo.sysjobs sj INNER JOIN msdb.dbo.sysjobhistory sh ON sj.job_id = sh.job_id left JOIN [msdb].[dbo].[sysjobsteps] AS [jstep] ON sj.job_id = jstep.job_id and sh.step_id=jstep.step_id CROSS APPLY ( SELECT RIGHT('000000' + CAST(sh.run_time AS VARCHAR(6)), 6), RIGHT('00000000' + CAST(sh.run_date AS VARCHAR(8)), 8) ) AS shp(padded_run_time, padded_run_date) where name='ATFACT_JOB' -- and sh.run_duration>='1000' -- 执行时长大于10分钟 -- and instance_id>=557203 order by instance_id desc GO |
分步:
1 2 3 4 5 6 7 8 | SELECT * FROM msdb.dbo.sysjobs d where d.name IN ('ATFACT_JOB','CDC_HIS_ZY'); SELECT * FROM msdb.dbo.sysjobhistory a where job_id IN ('65AE97BA-87C5-4B07-90C8-6615C8F8829F','E48BF8E1-60B5-4174-AC53-77FBF1DF03CA') -- and a.run_duration>=10*60*1000 order by instance_id desc |
查询作业基本信息和作业执行情况
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | SELECT [jop].[job_id] AS '作业唯一标识符' ,[jop].[name] AS '作业名称' ,[dp].[name] AS '作业创建者' ,[cat].[name] AS '作业类别' ,[jop].[description] AS '作业描述' , CASE [jop].[enabled] WHEN 1 THEN '是' WHEN 0 THEN '否' END AS '是否启用' ,[jop].[date_created] AS '作业创建日期' ,[jop].[date_modified] AS '作业最后修改日期' ,[sv].[name] AS '作业运行服务器名称' ,[step].[step_id] AS '作业起始步骤' ,[step].[step_name] AS '步骤名称' , CASE WHEN [sch].[schedule_uid] IS NULL THEN '否' ELSE '是' END AS '是否分布式作业' ,[sch].[schedule_uid] AS '作业计划的唯一标识符' ,[sch].[name] AS '作业计划的用户定义名称' , CASE [jop].[delete_level] WHEN 0 THEN '不删除' WHEN 1 THEN '成功后删除' WHEN 2 THEN '失败后删除' WHEN 3 THEN '完成后删除' END AS '作业完成删除选项' FROM [msdb].[dbo].[sysjobs] AS [jop] LEFT JOIN [msdb].[sys].[servers] AS [sv] ON [jop].[originating_server_id] = [sv].[server_id] LEFT JOIN [msdb].[dbo].[syscategories] AS [cat] ON [jop].[category_id] = [cat].[category_id] LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [step] ON [jop].[job_id] = [step].[job_id] AND [jop].[start_step_id] = [step].[step_id] LEFT JOIN [msdb].[sys].[database_principals] AS [dp] ON [jop].[owner_sid] = [dp].[sid] LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [jsch] ON [jop].[job_id] = [jsch].[job_id] LEFT JOIN [msdb].[dbo].[sysschedules] AS [sch] ON [jsch].[schedule_id] = [sch].[schedule_id] ORDER BY [jop].[name] |