在讲解SQLServer Agent Jobs之前,先要讲解msdb。
Msdb是SQLServer的系统数据库之一,用于存储SQLServer的配置、元数据等信息。包括:
l SQLServer Agent Jobs,Job Steps,Job schedules,Alerts,Operators,等等。
l Service Broker,Log Shipping,Backups/restore信息,维护计划、数据库邮件、基于策略管理信息等等。
l SSIS包。
在这部分,主要集中在msdb的以下部分:
l Job setup/configuration Information
l Job Execution Information
l Job Step(s) Setup/Configuration Information
l Job Step(s) Execution Information
l Schedule Information
————————————————————————————————————————————————————————————————————————————
SQLServer 允许在Replication,SSIS,存储过程,批处理上创建和执行各种自动化任务。允许使用GUI 或者T-SQL脚本创建。这些信息存储在msdb中。SQL Server Agent Job Setup andConfiguration Information:
可以在SSMS中执行以下脚本查找作业信息:
SELECT [sJOB].[job_id] AS [作业ID] ,
[sJOB].[name] AS [作业名称] ,
[sDBP].[name] AS [作业创建者] ,
[sCAT].[name] AS [作业种类] ,
[sJOB].[description] AS [作业描述] ,
CASE [sJOB].[enabled]
WHEN 1 THEN '已启用'
WHEN 0 THEN '未启用'
END AS [是否启用] ,--
[sJOB].[date_created] AS [作业创建日期] ,
[sJOB].[date_modified] AS [作业最后修改日期] ,
[sSVR].[name] AS [作业运行服务器] ,
[sJSTP].[step_id] AS [作业起始步骤] ,
[sJSTP].[step_name] AS [步骤名称] ,
CASE WHEN [sSCH].[schedule_uid] IS NULL THEN '否'
ELSE '是'
END AS [是否分布式作业] ,
[sSCH].[schedule_uid] AS [分布式作业ID] ,
[sSCH].[name] AS [用户定义名称] ,
CASE [sJOB].[delete_level]
WHEN 0 THEN '不删除'
WHEN 1 THEN '成功后删除'
WHEN 2 THEN '失败后删除'
WHEN 3 THEN '完成时删除'
END AS [完成时删除作业级别]
FROM [msdb].[dbo].[sysjobs] AS [sJOB]
LEFT JOIN [msdb].[sys].[servers] AS [sSVR] ON [sJOB].[originating_server_id] = [sSVR].[server_id]
LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT] ON [sJOB].[category_id] = [sCAT].[category_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP] ON [sJOB].[job_id] = [sJSTP].[job_id]
AND [sJOB].[start_step_id] = [sJSTP].[step_id]
LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP] ON [sJOB].[owner_sid] = [sDBP].[sid]
LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH] ON [sJOB].[job_id] = [sJOBSCH].[job_id]
LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH] ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
ORDER BY [作业名称]
SQL Server Agent Job Execution Information:
SQLServer同时存放作业执行信息在msdb中。可以执行以下脚本查询作业执行情况:
SELECT [sJOB].[job_id] AS [作业ID] ,
[sJOB].[name] AS [作业名] ,
CASE WHEN [sJOBH].[run_date] IS NULL
OR [sJOBH].[run_time] IS NULL THEN NULL
ELSE CAST(CAST([sJOBH].[run_date] AS CHAR(8)) + ' '
+ STUFF(STUFF(RIGHT('000000'
+ CAST([sJOBH].[run_time] AS VARCHAR(6)),
6), 3, 0, ':'), 6, 0, ':') AS DATETIME)
END AS [最近执行时间] ,
CASE [sJOBH].[run_status]
WHEN 0 THEN '失败'
WHEN 1 THEN '成功'
WHEN 2 THEN '重试'
WHEN 3 THEN '取消'
WHEN 4 THEN '正在运行' -- In Progress
Msdb是SQLServer的系统数据库之一,用于存储SQLServer的配置、元数据等信息。包括:
l SQLServer Agent Jobs,Job Steps,Job schedules,Alerts,Operators,等等。
l Service Broker,Log Shipping,Backups/restore信息,维护计划、数据库邮件、基于策略管理信息等等。
l SSIS包。
在这部分,主要集中在msdb的以下部分:
l Job setup/configuration Information
l Job Execution Information
l Job Step(s) Setup/Configuration Information
l Job Step(s) Execution Information
l Schedule Information
————————————————————————————————————————————————————————————————————————————
SQLServer 允许在Replication,SSIS,存储过程,批处理上创建和执行各种自动化任务。允许使用GUI 或者T-SQL脚本创建。这些信息存储在msdb中。SQL Server Agent Job Setup andConfiguration Information:
可以在SSMS中执行以下脚本查找作业信息:
SELECT [sJOB].[job_id] AS [作业ID] ,
[sJOB].[name] AS [作业名称] ,
[sDBP].[name] AS [作业创建者] ,
[sCAT].[name] AS [作业种类] ,
[sJOB].[description] AS [作业描述] ,
CASE [sJOB].[enabled]
WHEN 1 THEN '已启用'
WHEN 0 THEN '未启用'
END AS [是否启用] ,--
[sJOB].[date_created] AS [作业创建日期] ,
[sJOB].[date_modified] AS [作业最后修改日期] ,
[sSVR].[name] AS [作业运行服务器] ,
[sJSTP].[step_id] AS [作业起始步骤] ,
[sJSTP].[step_name] AS [步骤名称] ,
CASE WHEN [sSCH].[schedule_uid] IS NULL THEN '否'
ELSE '是'
END AS [是否分布式作业] ,
[sSCH].[schedule_uid] AS [分布式作业ID] ,
[sSCH].[name] AS [用户定义名称] ,
CASE [sJOB].[delete_level]
WHEN 0 THEN '不删除'
WHEN 1 THEN '成功后删除'
WHEN 2 THEN '失败后删除'
WHEN 3 THEN '完成时删除'
END AS [完成时删除作业级别]
FROM [msdb].[dbo].[sysjobs] AS [sJOB]
LEFT JOIN [msdb].[sys].[servers] AS [sSVR] ON [sJOB].[originating_server_id] = [sSVR].[server_id]
LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT] ON [sJOB].[category_id] = [sCAT].[category_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP] ON [sJOB].[job_id] = [sJSTP].[job_id]
AND [sJOB].[start_step_id] = [sJSTP].[step_id]
LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP] ON [sJOB].[owner_sid] = [sDBP].[sid]
LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH] ON [sJOB].[job_id] = [sJOBSCH].[job_id]
LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH] ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
ORDER BY [作业名称]
SQL Server Agent Job Execution Information:
SQLServer同时存放作业执行信息在msdb中。可以执行以下脚本查询作业执行情况:
SELECT [sJOB].[job_id] AS [作业ID] ,
[sJOB].[name] AS [作业名] ,
CASE WHEN [sJOBH].[run_date] IS NULL
OR [sJOBH].[run_time] IS NULL THEN NULL
ELSE CAST(CAST([sJOBH].[run_date] AS CHAR(8)) + ' '
+ STUFF(STUFF(RIGHT('000000'
+ CAST([sJOBH].[run_time] AS VARCHAR(6)),
6), 3, 0, ':'), 6, 0, ':') AS DATETIME)
END AS [最近执行时间] ,
CASE [sJOBH].[run_status]
WHEN 0 THEN '失败'
WHEN 1 THEN '成功'
WHEN 2 THEN '重试'
WHEN 3 THEN '取消'
WHEN 4 THEN '正在运行' -- In Progress