SQL Server 作业监控[转]
在讲解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中执行以下脚本查找作业信息：
view plaincopyprint?
[*]SELECT . AS [作业ID] ,
[*] .[name] AS [作业名称] ,
[*] .[name] AS [作业创建者] ,
[*] .[name] AS [作业种类] ,
[*] . AS [作业描述] ,
[*] CASE .
[*] WHEN 1 THEN '已启用'
[*] WHEN 0 THEN '未启用'
[*] END AS [是否启用] ,--
[*] . AS [作业创建日期] ,
[*] . AS [作业最后修改日期] ,
[*] .[name] AS [作业运行服务器] ,
[*] . AS [作业起始步骤] ,
[*] . AS [步骤名称] ,
[*] CASE WHEN . IS NULL THEN '否'
[*] ELSE '是'
[*] END AS [是否分布式作业] ,
[*] . AS [分布式作业ID] ,
[*] .[name] AS [用户定义名称] ,
[*] CASE .
[*] WHEN 0 THEN '不删除'
[*] WHEN 1 THEN '成功后删除'
[*] WHEN 2 THEN '失败后删除'
[*] WHEN 3 THEN '完成时删除'
[*] END AS [完成时删除作业级别]
[*]FROM .. AS
[*] LEFT JOIN .. AS ON . = .
[*] LEFT JOIN .. AS ON . = .
[*] LEFT JOIN .. AS ON . = .
[*] AND . = .
[*] LEFT JOIN .. AS ON . = .
[*] LEFT JOIN .. AS ON . = .
[*] LEFT JOIN .. AS ON . = .
[*]ORDER BY [作业名称]
SQL Server Agent Job Execution Information：
SQLServer同时存放作业执行信息在msdb中。可以执行以下脚本查询作业执行情况：
view plaincopyprint?
[*]SELECT . AS [作业ID] ,
[*] .[name] AS [作业名] ,
[*] CASE WHEN . IS NULL
[*] OR . IS NULL THEN NULL
[*] ELSE CAST(CAST(. AS CHAR(8)) + ' '
[*] + STUFF(STUFF(RIGHT('000000'
[*] + CAST(. AS VARCHAR(6)),
[*] 6), 3, 0, ':'), 6, 0, ':') AS DATETIME)
[*] END AS [最近执行时间] ,
[*] CASE .
[*] WHEN 0 THEN '失败'
[*] WHEN 1 THEN '成功'
[*] WHEN 2 THEN '重试'
[*] WHEN 3 THEN '取消'
[*] WHEN 4 THEN '正在运行' -- In Progress
[*] END AS [最近执行状态] ,
[*] STUFF(STUFF(RIGHT('000000'
[*] + CAST(. AS VARCHAR(6)), 6), 3,
[*] 0, ':'), 6, 0, ':') AS ,
[*] . AS [最近运行状态信息] ,
[*] CASE .
[*] WHEN 0 THEN NULL
[*] ELSE CAST(CAST(. AS CHAR(8)) + ' '
[*] + STUFF(STUFF(RIGHT('000000'
[*] + CAST(. AS VARCHAR(6)),
[*] 6), 3, 0, ':'), 6, 0, ':') AS DATETIME)
[*] END AS [下次运行时间]
[*]FROM .. AS
[*] LEFT JOIN ( SELECT ,
[*] MIN() AS ,
[*] MIN() AS
[*] FROM ..
[*] GROUP BY
[*] ) AS ON . = .
[*] LEFT JOIN ( SELECT ,
[*] ,
[*] ,
[*] ,
[*] ,
[*] ,
[*] ROW_NUMBER() OVER ( PARTITION BY ORDER BY DESC, DESC ) AS RowNumber
[*] FROM ..
[*] WHERE = 0
[*] ) AS ON . = .
[*] AND . = 1
[*]ORDER BY [作业名]
SQL Server Anget Job Steps Setup andconfiguration Information：
在作业系统中，一个作业是有层级的，可以包含一个或多个步骤。
运行以下脚本查看作业步骤信息：
view plaincopyprint?
[*]SELECT . AS [作业ID] ,
[*] .[name] AS [作业名] ,
[*] . AS [步骤ID] ,
[*] . AS [步骤序号] ,
[*] . AS [步骤名] ,
[*] CASE .
[*] WHEN 'ActiveScripting' THEN 'ActiveX Script'
[*] WHEN 'CmdExec' THEN 'Operating system (CmdExec)'
[*] WHEN 'PowerShell' THEN 'PowerShell'
[*] WHEN 'Distribution' THEN 'Replication Distributor'
[*] WHEN 'Merge' THEN 'Replication Merge'
[*] WHEN 'QueueReader' THEN 'Replication Queue Reader'
[*] WHEN 'Snapshot' THEN 'Replication Snapshot'
[*] WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'
[*] WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'
[*] WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'
[*] WHEN 'SSIS' THEN 'SQL Server Integration Services Package'
[*] WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'
[*] ELSE sJSTP.subsystem
[*] END AS [作业子系统类型] ,
[*] .[name] AS [作业运行账号] ,
[*] . AS [执行数据库名] ,
[*] . AS [执行命令] ,
[*] CASE .
[*] WHEN 1 THEN 'Quit the job reporting success'
[*] WHEN 2 THEN 'Quit the job reporting failure'
[*] WHEN 3 THEN 'Go to the next step'
[*] WHEN 4
[*] THEN 'Go to Step: '
[*] + QUOTENAME(CAST(. AS VARCHAR(3)))
[*] + ' ' + .
[*] END AS [执行成功后反应] ,
[*] . AS [失败时的重试次数] ,
[*] . AS [重试间的等待时间 (Minutes)] ,
[*] CASE .
[*] WHEN 1 THEN 'Quit the job reporting success'
[*] WHEN 2 THEN 'Quit the job reporting failure'
[*] WHEN 3 THEN 'Go to the next step'
[*] WHEN 4
[*] THEN 'Go to Step: '
[*] + QUOTENAME(CAST(. AS VARCHAR(3)))
[*] + ' ' + .
[*] END AS [执行失败后反映]
[*]FROM .. AS
[*] INNER JOIN .. AS ON . = .
[*] LEFT JOIN .. AS ON . = .
[*] AND . = .
[*] LEFT JOIN .. AS ON . = .
[*] AND . = .
[*] LEFT JOIN .. AS ON . = .
[*]ORDER BY [作业名] ,
[*] [步骤序号]
SQL Server Anget Job Steps ExecutionInformation：
在msdb中同样存储了步骤的执行计划，执行以下语句检查：
view plaincopyprint?
[*]SELECT . AS [作业ID] ,
[*] .[name] AS [作业名称] ,
[*] . AS [步骤ID] ,
[*] . AS [步骤序号] ,
[*] . AS [步骤名称] ,
[*] CASE .
[*] WHEN 0 THEN '失败'
[*] WHEN 1 THEN '成功'
[*] WHEN 2 THEN '重试'
[*] WHEN 3 THEN '取消'
[*] WHEN 5 THEN '未知'
[*] END AS [上次运行状态] ,
[*] STUFF(STUFF(RIGHT('000000'
[*] + CAST(. AS VARCHAR(6)), 6),
[*] 3, 0, ':'), 6, 0, ':') AS ,
[*] . AS [上次重试次数] ,
[*] CASE .
[*] WHEN 0 THEN NULL
[*] ELSE CAST(CAST(. AS CHAR(8)) + ' '
[*] + STUFF(STUFF(RIGHT('000000'
[*] + CAST(. AS VARCHAR(6)),
[*] 6), 3, 0, ':'), 6, 0, ':') AS DATETIME)
[*] END AS [上次运行时间]
[*]FROM .. AS
[*] INNER JOIN .. AS ON . = .
[*]ORDER BY [作业名称] ,
[*] [步骤序号]
SQL Server Agent Job Sechdule Information：
SQLServer允许在特定时间创建各种计划，每个计划能组合成一个或多个SQLServer Agent Jobs。执行以下脚本查询情况：
view plaincopyprint?
[*]SELECT AS [作业计划ID] ,
[*] [name] AS [作业计划名称] ,
[*] CASE
[*] WHEN 1 THEN '已启用'
[*] WHEN 0 THEN '未启用'
[*] END AS [是否启用] ,
[*] CASE WHEN = 64
[*] THEN 'Start automatically when SQL Server Agent starts'
[*] WHEN = 128 THEN 'Start whenever the CPUs become idle'
[*] WHEN IN ( 4, 8, 16, 32 ) THEN 'Recurring'
[*] WHEN = 1 THEN 'One Time'
[*] END [作业计划类型] ,
[*] CASE
[*] WHEN 1 THEN 'One Time'
[*] WHEN 4 THEN 'Daily'
[*] WHEN 8 THEN 'Weekly'
[*] WHEN 16 THEN 'Monthly'
[*] WHEN 32 THEN 'Monthly - Relative to Frequency Interval'
[*] WHEN 64 THEN 'Start automatically when SQL Server Agent starts'
[*] WHEN 128 THEN 'Start whenever the CPUs become idle'
[*] END [作业运行频率] ,
[*] CASE
[*] WHEN 4
[*] THEN 'Occurs every ' + CAST( AS VARCHAR(3))
[*] + ' day(s)'
[*] WHEN 8
[*] THEN 'Occurs every ' + CAST( AS VARCHAR(3))
[*] + ' week(s) on '
[*] + CASE WHEN & 1 = 1 THEN 'Sunday'
[*] ELSE ''
[*] END + CASE WHEN & 2 = 2 THEN ', Monday'
[*] ELSE ''
[*] END
[*] + CASE WHEN & 4 = 4 THEN ', Tuesday'
[*] ELSE ''
[*] END + CASE WHEN & 8 = 8 THEN ', Wednesday'
[*] ELSE ''
[*] END
[*] + CASE WHEN & 16 = 16 THEN ', Thursday'
[*] ELSE ''
[*] END + CASE WHEN & 32 = 32 THEN ', Friday'
[*] ELSE ''
[*] END
[*] + CASE WHEN & 64 = 64 THEN ', Saturday'
[*] ELSE ''
[*] END
[*] WHEN 16
[*] THEN 'Occurs on Day ' + CAST( AS VARCHAR(3))
[*] + ' of every ' + CAST( AS VARCHAR(3))
[*] + ' month(s)'
[*] WHEN 32
[*] THEN 'Occurs on ' + CASE
[*] WHEN 1 THEN 'First'
[*] WHEN 2 THEN 'Second'
[*] WHEN 4 THEN 'Third'
[*] WHEN 8 THEN 'Fourth'
[*] WHEN 16 THEN 'Last'
[*] END + ' ' + CASE
[*] WHEN 1 THEN 'Sunday'
[*] WHEN 2 THEN 'Monday'
[*] WHEN 3 THEN 'Tuesday'
[*] WHEN 4 THEN 'Wednesday'
[*] WHEN 5 THEN 'Thursday'
[*] WHEN 6 THEN 'Friday'
[*] WHEN 7 THEN 'Saturday'
[*] WHEN 8 THEN 'Day'
[*] WHEN 9 THEN 'Weekday'
[*] WHEN 10 THEN 'Weekend day'
[*] END + ' of every '
[*] + CAST( AS VARCHAR(3)) + ' month(s)'
[*] END AS [循环间隔] ,
[*] CASE
[*] WHEN 1
[*] THEN 'Occurs once at ' + STUFF(STUFF(RIGHT('000000'
[*] + CAST( AS VARCHAR(6)),
[*] 6), 3, 0, ':'), 6, 0, ':')
[*] WHEN 2
[*] THEN 'Occurs every ' + CAST( AS VARCHAR(3))
[*] + ' Second(s) between ' + STUFF(STUFF(RIGHT('000000'
[*] + CAST( AS VARCHAR(6)),
[*] 6), 3, 0, ':'), 6,
[*] 0, ':') + ' & '
[*] + STUFF(STUFF(RIGHT('000000'
[*] + CAST( AS VARCHAR(6)), 6),
[*] 3, 0, ':'), 6, 0, ':')
[*] WHEN 4
[*] THEN 'Occurs every ' + CAST( AS VARCHAR(3))
[*] + ' Minute(s) between ' + STUFF(STUFF(RIGHT('000000'
[*] + CAST( AS VARCHAR(6)),
[*] 6), 3, 0, ':'), 6,
[*] 0, ':') + ' & '
[*] + STUFF(STUFF(RIGHT('000000'
[*] + CAST( AS VARCHAR(6)), 6),
[*] 3, 0, ':'), 6, 0, ':')
[*] WHEN 8
[*] THEN 'Occurs every ' + CAST( AS VARCHAR(3))
[*] + ' Hour(s) between ' + STUFF(STUFF(RIGHT('000000'
[*] + CAST( AS VARCHAR(6)),
[*] 6), 3, 0, ':'), 6, 0,
[*] ':') + ' & '
[*] + STUFF(STUFF(RIGHT('000000'
[*] + CAST( AS VARCHAR(6)), 6),
[*] 3, 0, ':'), 6, 0, ':')
[*] END [计划运行频率] ,
[*] STUFF(STUFF(CAST( AS VARCHAR(8)), 5, 0, '-'), 8, 0,
[*] '-') AS [作业启用开始时间] ,
[*] STUFF(STUFF(CAST( AS VARCHAR(8)), 5, 0, '-'), 8, 0,
[*] '-') AS [作业启用结束时间] ,
[*] AS [作业创建日期] ,
[*] AS [作业上次修改日期]
[*]FROM ..
[*]ORDER BY [作业计划名称]
页:
[1]