SQL SERVER 作业浅析

plantegg

　　作业介绍
　　    SQL SERVER的作业是一系列由SQL SERVER代理按顺序执行的指定操作。作业可以执行一系列活动，包括运行Transact-SQL脚本、命令行应用程序、Microsoft ActiveX脚本、Integration Services 包、Analysis Services 命令和查询或复制任务。作业可以运行重复任务或那些可计划的任务，它们可以通过生成警报来自动通知用户作业状态，从而极大地简化了 SQL Server 管理[参见MSDN]。
　　    创建作业、删除作业、查看作业历史记录....等所有操作都可以通过SSMS管理工具GUI界面操作，有时候也确实挺方便的。但是当一个实例有多个作业或多个数据库实例时，通过图形化的界面去管理、维护作业也是个头痛的问题，对于SQL脚本与GUI界面管理维护作业熟优熟劣这个问题，只能说要看场合。下面主要介绍通过SQL脚本来管理、维护作业。
　　作业分类
　　    创建作业时，往往需要指定作业类别，如果不指定新建作业类别，就会默认为“[未分类(本地)]”，如下截图所示：
　　
　　
　　    当然，你可以查看、添加、删除、修改作业分类。请看下面操作。
　　1：查看作业分类






Code Snippet

• --method 1:
  
• EXEC msdb.dbo.sp_help_category;
  
• 
  
• GO
  
• 
  
• --method 2:
  
•   SELECT  category_id            ,--作业类别ID
  
•           category_class         ,--类别中项目类型：1=作业2=警报 3=操作员
  
•           category_type          ,--类别中类型：=本地、=多服务器、=无
  
•           name                    --分类名称
  
•     FROMmsdb.dbo.syscategories
  

　　有兴趣的可以研究一下存储过程msdb.dbo.sp_help_category





SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_help_category
@class  VARCHAR(8)   = 'JOB',
@type   VARCHAR(12)  = NULL,
@name   sysname      = NULL,
@suffix BIT          = 0
AS
BEGIN
DECLARE @retval         INT
DECLARE @type_in        VARCHAR(12)
DECLARE @category_type  INT
DECLARE @category_class INT
DECLARE @where_clause   NVARCHAR(255)
DECLARE @cmd            NVARCHAR(255)
SET NOCOUNT ON
SELECT @class = LTRIM(RTRIM(@class))
SELECT @type  = LTRIM(RTRIM(@type))
SELECT @name  = LTRIM(RTRIM(@name))
IF (@type = '') SELECT @type = NULL
IF (@name = N'') SELECT @name = NULL
IF (@class = 'JOB') AND (@type IS NULL)
SELECT @type_in = 'LOCAL'
ELSE
IF (@class  'JOB') AND (@type IS NULL)
SELECT @type_in = 'NONE'
ELSE
SELECT @type_in = @type
EXECUTE @retval = sp_verify_category @class,
@type_in,
NULL,
@category_class OUTPUT,
@category_type  OUTPUT
IF (@retval  0)
RETURN(1)
IF (@suffix  0)
SELECT @suffix = 1
IF @name IS NOT NULL AND
NOT EXISTS(SELECT * FROM msdb.dbo.syscategories WHERE name = @name
AND category_class = @category_class)
BEGIN
DECLARE @category_class_string NVARCHAR(25)
SET @category_class_string = CAST(@category_class AS nvarchar(25))
RAISERROR(14526, -1, -1, @name, @category_class_string)
RETURN(1)
END
SELECT @where_clause = N'WHERE (category_class = ' + CONVERT(NVARCHAR, @category_class) + N') '
IF (@name IS NOT NULL)
SELECT @where_clause = @where_clause + N'AND (name = N' + QUOTENAME(@name, '''') + N') '
IF (@type IS NOT NULL)
SELECT @where_clause = @where_clause + N'AND (category_type = ' + CONVERT(NVARCHAR, @category_type) + N') '
SELECT @cmd = N'SELECT category_id, '
IF (@suffix = 1)
BEGIN
SELECT @cmd = @cmd + N'''category_type'' = '
SELECT @cmd = @cmd + N'CASE category_type '
SELECT @cmd = @cmd + N'WHEN 0 THEN ''NONE'' '
SELECT @cmd = @cmd + N'WHEN 1 THEN ''LOCAL'' '
SELECT @cmd = @cmd + N'WHEN 2 THEN ''MULTI-SERVER'' '
SELECT @cmd = @cmd + N'WHEN 3 THEN ''NONE'' '
SELECT @cmd = @cmd + N'ELSE FORMATMESSAGE(14205) '
SELECT @cmd = @cmd + N'END, '
END
ELSE
BEGIN
SELECT @cmd = @cmd + N'category_type, '
END
SELECT @cmd = @cmd + N'name '
SELECT @cmd = @cmd + N'FROM msdb.dbo.syscategories '
EXECUTE (@cmd + @where_clause + N'ORDER BY category_type, name')
RETURN(@@error)
END
GO
sp_help_category　　
　　2：添加作业分类
　　如下所示，添加一个叫"DBA_MONITORING"的作业分类






Code Snippet

• EXEC msdb.dbo.sp_add_category
  
•     @class=N'JOB',
  
•     @type=N'LOCAL',
  
•     @name=N'DBA_MONITORING' ;
  
• 
  
• GO
  
• 
  
• 
  
• 
  
• SELECT * FROM msdb.dbo.syscategories WHERE NAME='DBA_MONITORING'
  
• 
  
• category_id category_class category_type    name
  
• ----------- -------------- ------------- -------------
  
• 102         1              1             DBA_MONITORING
  

　　
　　有兴趣的可以研究一下存储过程msdb.dbo.sp_add_category





SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_add_category
@class VARCHAR(8)   = 'JOB',
@type  VARCHAR(12)  = 'LOCAL',
@name  sysname
AS
BEGIN
DECLARE @retval         INT
DECLARE @category_type  INT
DECLARE @category_class INT
SET NOCOUNT ON
SELECT @class = LTRIM(RTRIM(@class))
SELECT @type  = LTRIM(RTRIM(@type))
SELECT @name  = LTRIM(RTRIM(@name))
EXECUTE @retval = sp_verify_category @class,
@type,
@name,
@category_class OUTPUT,
@category_type  OUTPUT
IF (@retval  0)
RETURN(1)
IF (EXISTS (SELECT *
FROM msdb.dbo.syscategories
WHERE (category_class = @category_class)
AND (name = @name)))
BEGIN
RAISERROR(14261, -1, -1, '@name', @name)
RETURN(1)
END
INSERT INTO msdb.dbo.syscategories (category_class, category_type, name)
VALUES (@category_class, @category_type, @name)
RETURN(@@error)
END
GO
sp_add_category　　3：删除作业分类
　　如下所示，删除一个叫"DBA_MONITORING" 的作业分类






Code Snippet

• EXEC msdb.dbo.sp_delete_category
  
•     @name = N'DBA_MONITORING',
  
•     @class = N'JOB' ;
  
• 
  
• GO
  

　　有兴趣的可以研究一下存储过程msdb.dbo.sp_delete_category





SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_delete_category
@class VARCHAR(8),
@name  sysname
AS
BEGIN
DECLARE @retval         INT
DECLARE @category_id    INT
DECLARE @category_class INT
DECLARE @category_type  INT
SET NOCOUNT ON
SELECT @class = LTRIM(RTRIM(@class))
SELECT @name  = LTRIM(RTRIM(@name))
EXECUTE @retval = sp_verify_category @class,
NULL,
NULL,
@category_class OUTPUT,
NULL
IF (@retval  0)
RETURN(1)
SELECT @category_id = category_id,
@category_type = category_type
FROM msdb.dbo.syscategories
WHERE (category_class = @category_class)
AND (name = @name)
IF (@category_id IS NULL)
BEGIN
RAISERROR(14262, -1, -1, '@name', @name)
RETURN(1)
END
IF (@category_id < 100)
BEGIN
RAISERROR(14276, -1, -1, @name, @class)
RETURN(1)
END
BEGIN TRANSACTION
UPDATE msdb.dbo.sysjobs
SET category_id = CASE @category_type
WHEN 1 THEN 0
WHEN 2 THEN 2
END
WHERE (category_id = @category_id)
UPDATE msdb.dbo.sysalerts
SET category_id = 98
WHERE (category_id = @category_id)
UPDATE msdb.dbo.sysoperators
SET category_id = 99
WHERE (category_id = @category_id)
DELETE FROM msdb.dbo.syscategories
WHERE (category_id = @category_id)
COMMIT TRANSACTION
RETURN(0)
END
GO
sp_delete_category　　4：修改作业类别
　　

msdb.dbo.sp_update_category      [@class =] 'class' ,      [@name  =] 'old_name' ,      [@new_name =] 'new_name'

　　有兴趣的可以研究一下存储过程msdb.dbo.sp_update_category





SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_update_category
@class    VARCHAR(8),
@name     sysname,
@new_name sysname
AS
BEGIN
DECLARE @retval         INT
DECLARE @category_id    INT
DECLARE @category_class INT
SET NOCOUNT ON
SELECT @class    = LTRIM(RTRIM(@class))
SELECT @name     = LTRIM(RTRIM(@name))
SELECT @new_name = LTRIM(RTRIM(@new_name))
IF @name = ''  SELECT @name = NULL
EXECUTE @retval = sp_verify_category @class,
NULL,
@new_name,
@category_class OUTPUT,
NULL
IF (@retval  0)
RETURN(1)
IF @name IS NOT NULL AND
NOT EXISTS(SELECT * FROM msdb.dbo.syscategories WHERE name = @name
AND category_class = @category_class)
BEGIN
RAISERROR(14526, -1, -1, @name, @category_class)
RETURN(1)
END
SELECT @category_id = category_id
FROM msdb.dbo.syscategories
WHERE (category_class = @category_class)
AND (name = @new_name)
IF (@category_id IS NOT NULL)
BEGIN
RAISERROR(14261, -1, -1, '@new_name', @new_name)
RETURN(1)
END
IF (@category_id < 100)
BEGIN
RAISERROR(14276, -1, -1, @name, @class)
RETURN(1)
END
UPDATE msdb.dbo.syscategories
SET name = @new_name
WHERE (category_class = @category_class)
AND (name = @name)
RETURN(@@error)
END
GO
sp_update_category　　
　　分析上面四个存储过程可以看出，实质上新增、修改、删除、查看作业类别无非就是对表 msdb.dbo.syscategories进行操作，只是通过存储过程封装了而已，增加了验证等操作，确保数据完整性。
　　新建作业
　　
　　创建作业的步骤一般如下所示：

• 执行 sp_add_job 来创建作业。
  
• 　　执行 sp_add_jobstep 来创建一个或多个作业步骤。
  
  
• 　　执行 sp_add_schedule 来创建计划。
  
  
• 　　执行 sp_attach_schedule 将计划附加到作业。
  
  
• 　　执行 sp_add_jobserver 来设置作业的服务器。
  
  

　　本地作业是由本地 SQL Server 代理进行缓存的。因此，任何修改都会隐式强制 SQL Server 代理重新缓存该作业。由于直到调用 sp_add_jobserver 时，SQL Server 代理才缓存作业，因此最后调用 sp_add_jobserver 将更为有效。
　　下面看用脚本新建一个作业用来每天执行exec sp_cycle_errorlog ，实现错误日志循环， 从下面的脚本量来看，用脚本新建一个作业确实工作量很大，而且容易出错，GUI图形界面创建作业要方便得多，但是如果迁移数据库时，用脚本来新建作业是相当方便的。比GUI图形界面新建一个作业快捷方便多了。






Code Snippet

• USE [msdb]
  
• GO
  
• 
  
• /****** Object:  Job [JOB_CYCLE_ERRORLOG]    Script Date: 08/23/2013 15:25:09 ******/
  
•   IFEXISTS(SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'JOB_CYCLE_ERRORLOG')
  
• EXEC msdb.dbo.sp_delete_job@job_id=N'a5dff08b-95f8-498e-a6c9-59241fe197b4', @delete_unused_schedule=1
  
• GO
  
• 
  
• USE [msdb]
  
• GO
  
• 
  
• /****** Object:  Job [JOB_CYCLE_ERRORLOG]    Script Date: 08/23/2013 15:25:09 ******/
  
• BEGIN TRANSACTION
  
• DECLARE @ReturnCode INT
  
• SELECT @ReturnCode = 0
  
• /****** Object:  JobCategory [DBA_MATIANCE]    Script Date: 08/23/2013 15:25:09 ******/
  
• IF NOT EXISTS(SELECT name FROM msdb.dbo.syscategories WHERE name=N'DBA_MATIANCE' AND category_class=1)
  
• BEGIN
  
• EXEC @ReturnCode = msdb.dbo.sp_add_category@class=N'JOB', @type=N'LOCAL', @name=N'DBA_MATIANCE'
  
• IF (@@ERROR  0 OR @ReturnCode  0) GOTO QuitWithRollback
  
• 
  
• END
  
• 
  
• DECLARE @jobId BINARY(16)
  
•   EXEC @ReturnCode =msdb.dbo.sp_add_job@job_name=N'JOB_CYCLE_ERRORLOG',
  
•         @enabled=1,
  
•         @notify_level_eventlog=0,
  
•         @notify_level_email=0,
  
•         @notify_level_netsend=0,
  
•         @notify_level_page=0,
  
•         @delete_level=0,
  
•         @description=N'每天执行exec sp_cycle_errorlog 实现错误日志循环。',
  
•         @category_name=N'DBA_MATIANCE',
  
•         @owner_login_name=N'sa', @job_id = @jobId OUTPUT
  
• IF (@@ERROR  0 OR @ReturnCode  0) GOTO QuitWithRollback
  
• /****** Object:  Step [Step 1: recycle the errorlog]    Script Date: 08/23/2013 15:25:09 ******/
  
• EXEC @ReturnCode = msdb.dbo.sp_add_jobstep@job_id=@jobId, @step_name=N'Step 1: recycle the errorlog',
  
•         @step_id=1,
  
•         @cmdexec_success_code=0,
  
•         @on_success_action=1,
  
•         @on_success_step_id=0,
  
•         @on_fail_action=2,
  
•         @on_fail_step_id=0,
  
•         @retry_attempts=0,
  
•         @retry_interval=0,
  
•         @os_run_priority=0, @subsystem=N'TSQL',
  
•         @command=N'exec msdb.dbo.sp_cycle_errorlog',
  
•         @database_name=N'msdb',
  
•         @flags=0
  
• IF (@@ERROR  0 OR @ReturnCode  0) GOTO QuitWithRollback
  
• EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
  
• IF (@@ERROR  0 OR @ReturnCode  0) GOTO QuitWithRollback
  
• EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Job Schedule',
  
•         @enabled=1,
  
•         @freq_type=4,
  
•         @freq_interval=1,
  
•         @freq_subday_type=1,
  
•         @freq_subday_interval=0,
  
•         @freq_relative_interval=0,
  
•         @freq_recurrence_factor=0,
  
•         @active_start_date=20130823,
  
•         @active_end_date=99991231,
  
•         @active_start_time=0,
  
•         @active_end_time=235959,
  
•         @schedule_uid=N'2099c694-cd26-4edf-8803-179227bf8770'
  
• IF (@@ERROR  0 OR @ReturnCode  0) GOTO QuitWithRollback
  
• EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
  
• IF (@@ERROR  0 OR @ReturnCode  0) GOTO QuitWithRollback
  
• COMMIT TRANSACTION
  
• GOTO EndSave
  
• QuitWithRollback:
  
•     IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
  
• EndSave:
  
• 
  
• GO
  

　　作业系统表
　　    与作业有关的系统表、视图大致有下面9个，下面就不费口舌详细解说每一个系统表的作用了，MSDN文档上有详细的解说，有兴趣的翻看一下即可。
　　
　　SELECT * FROM msdb.dbo.sysjobs            --存储将由 SQL Server 代理执行的各个预定作业的信息
　　SELECT * FROM msdb.dbo.sysjobschedules    --包含将由 SQL Server 代理执行的作业的计划信息
　　SELECT * FROM msdb.dbo.sysjobactivity;    --记录当前 SQL Server 代理作业活动和状态
　　SELECT * FROM msdb.dbo.sysjobservers      --存储特定作业与一个或多个目标服务器的关联或关系
　　SELECT * FROM msdb.dbo.sysjobsteps;       --包含 SQL Server 代理要执行的作业中的各个步骤的信息
　　SELECT * FROM msdb.dbo.sysjobstepslogs;   --包含所有 SQL Server 代理作业步骤的作业步骤日志
　　SELECT * FROM msdb.dbo.sysjobs_view;      --
　　SELECT * FROM msdb.dbo.sysjobhistory      --包含有关 SQL Server 代理执行预定作业的信息
　　SELECT * FROM msdb.dbo.syscategories      --包含由 SQL Server Management Studio 用来组织作业、警报和操作员的类别
　　运行作业
　　
　　启动作业
　　        1：通过SSMS工具启动作业[参见MSDN]
　　        2：通过SQL命令启动作业
　　启动作业一般通过sp_start_job来实现，具体语法与操作见下面。



语法:
sp_start_job
{   [@job_name =] 'job_name'
| [@job_id =] job_id }
[ , [@error_flag =] error_flag]
[ , [@server_name =] 'server_name']
[ , [@step_name =] 'step_name']
[ , [@output_flag =] output_flag]
例子:
exec msdb.dbo.sp_start_job @job_name='JOB_CYCLE_ERRORLOG'
　　
　　停止作业
　　        1：通过SSMS工具停作业[参见MSDN]
　　        2：通过SQL命令停止作业



语法:
sp_stop_job
[@job_name =] 'job_name'
| [@job_id =] job_id
| [@originating_server =] 'master_server'
| [@server_name =] 'target_server'
例子:
exec msdb.dbo.sp_stop_job    @job_name='JOB_CYCLE_ERRORLOG'
　　
　　启用或禁用作业
　　
　　        1：通过SSMS工具启用作业[参见MSDN]
　　        2：通过SQL命令禁用作业



语法:
sp_update_job [ @job_id =] job_id | [@job_name =] 'job_name'
[, [@new_name =] 'new_name' ]
[, [@enabled =] enabled ]
[, [@description =] 'description' ]
[, [@start_step_id =] step_id ]
[, [@category_name =] 'category' ]
[, [@owner_login_name =] 'login' ]
[, [@notify_level_eventlog =] eventlog_level ]
[, [@notify_level_email =] email_level ]
[, [@notify_level_netsend =] netsend_level ]
[, [@notify_level_page =] page_level ]
[, [@notify_email_operator_name =] 'email_name' ]
[, [@notify_netsend_operator_name =] 'netsend_operator' ]
[, [@notify_page_operator_name =] 'page_operator' ]
[, [@delete_level =] delete_level ]
[, [@automatic_post =] automatic_post ]
列子：

EXEC msdb.dbo.sp_update_job
@job_name = N'JOB_CYCLE_ERRORLOG',
@enabled = 0 ;  --0 禁用作业、  1启用作业
GO
　　
　　删除作业
　　1：通过SSMS工具删除作业[参见MSDN]
　　2：通过SQL命令删除作业



语法：
sp_delete_job { [ @job_id = ] job_id | [ @job_name = ] 'job_name' } , [ , [ @originating_server = ] 'server' ] [ , [ @delete_history = ] delete_history ] [ , [ @delete_unused_schedule = ] delete_unused_schedule ]
例子：
EXEC msdb.dbo.sp_delete_job  @job_name = 'JOB_CYCLE_ERRORLOG';

　　
　　迁移作业

使用 Transact-SQL 编写作业脚本

• 　　在对象资源管理器中，连接到 Microsoft SQL Server 数据库引擎实例，再展开该实例。
  
  
• 　　展开“SQL Server 代理”，再展开“作业”，然后右键单击要编写脚本的作业。
  
  
• 　　从快捷菜单中，选择“编写作业脚本为”，再选择“CREATE 到”或“DROP 到”，并单击下列内容之一：
  　　新查询编辑器窗口，将打开一个新的查询编辑器窗口，并为其编写 Transact-SQL 脚本。
  　　文件，将 Transact-SQL 脚本保存到文件。
  
  

　　      剪贴板，将 Transact-SQL 脚本保存到剪贴板
　　
　　常用管理作业SQL
　　
　　1:查看属于某个数据库的所有作业。
　　






Code Snippet

• SELECT j.job_id        AS JOB_ID            ,
  
•        name            AS JOB_NAME          ,
  
•        enabled         AS JOB_ENABLED       ,
  
•        description     AS JOB_DESCRIPTION   ,
  
•        date_created    AS DATE_CREATED      ,
  
•        date_modified   AS DATE_MODIFIED
  
• FROM msdb.dbo.sysjobs j
  
• WHERE   job_id IN( SELECTjob_id
  
•                   FROM    msdb.dbo.sysjobsteps
  
•                   WHERE   database_name = 'DataBaseName' )
  

　　
　　2:查看某个作业类别的所有作业
　　






Code Snippet

• SELECT　　j.name             AS Job_Name       ,
  
•          j.description      AS Job_Description ,
  
•          j.date_created     AS Date_Created    ,
  
•          j.date_modified    AS Date_Modified   ,
  
•          c.name             AS Job_Class
  
• FROM  msdb.dbo.sysjobs j
  
•       LEFT JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
  
• WHEREc.name = '[Uncategorized (Local)]'
  

　　
　　3:查看禁用/启用的作业
　　SELECT * FROM msdb.dbo.sysjobs WHERE  enabled=0    --0：禁用 1：为启用
　　
　　4:查看出错的作业记录
　　
　　4.1：查询那些作业在今天出错（如果要查询历史出错作业，去掉查询时间条件即可）






Code Snippet

• SELECT name               AS JOB_NAME        ,
  
•       description         AS JOB_Description ,
  
•       date_created        AS Date_Created    ,
  
•       date_modified       AS Date_Modified
  
• FROM  msdb.dbo.sysjobs
  
• WHERE enabled = 1
  
•       AND job_id IN(
  
•       SELECT  job_id
  
•       FROM    Msdb.dbo.sysjobhistory
  
•       WHERE   run_status = 0
  
•               AND run_date = CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS INT) )
  

　　
　　4.2：查看出错详细信息






Code Snippet

• SELECT j.name                AS JOB_NAME ,
  
•       h.step_id              AS STEP_ID  ,
  
•       h.step_name            AS STEP_NAME,
  
•       h.message              AS ERR_MSG  ,
  
•       h.run_date             AS RUN_DATE ,
  
•       h.run_time             AS RUN_TIME ,
  
•       msdb.dbo.agent_datetime(h.run_date, h.run_time) AS 'RunDateTime' ,
  
•       CAST(run_duration / 10000 AS VARCHAR(2)) + N'小时'
  
•     + CAST(( run_duration - run_duration / 10000 * 10000 ) / 100 AS VARCHAR(2))
  
•     + N'分钟' + SUBSTRING(CAST(run_duration AS VARCHAR(10)),
  
•                           LEN(CAST(run_duration AS VARCHAR(10))) - 1, 2)
  
•     + N'秒' AS run_duration
  
• FROM  msdb.dbo.sysjobhistory h
  
•       LEFT JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
  
• WHERE run_status = 0
  
•       AND run_date = CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS INT)
  

　　
　　5：查看作业的执行时间:
　　5.1:查看当天成功执行的作业的时间（查看的是作业Step信息）






Code Snippet

• SELECT j.name         AS job_name ,
  
•       h.step_id 　　   AS step_id  ,
  
•       h.step_name     AS step_name,
  
•       h.message       AS Message  ,
  
•       h.run_date      AS Run_date ,
  
•       h.run_time      AS run_time ,
  
•       msdb.dbo.agent_datetime(h.run_date, h.run_time) AS 'RunDateTime' ,
  
•       CAST(run_duration / 10000 AS VARCHAR(2)) + N'小时'
  
•       + CAST(( run_duration - run_duration / 10000 * 10000 ) / 100 AS VARCHAR(2))
  
•       + N'分钟' + SUBSTRING(CAST(run_duration AS VARCHAR(10)),
  
•                           LEN(CAST(run_duration AS VARCHAR(10))) - 1, 2)
  
•       + N'秒' AS run_duration
  
• FROM msdb.dbo.sysjobhistory h
  
•       LEFT JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
  
• WHERE run_status = 1
  
•       AND run_date = CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS INT)
  
•       ORDER BY run_duration DESC
  

　　
　　5.2：查询每个作业的执行时间、按执行时间降序






Code Snippet

• SELECT  j.name            AS JOB_NAME ,
  
•         h.run_date        AS RUN_DATE ,
  
•         SUM(run_duration) AS SUM_DURATION
  
• FROM  msdb.dbo.sysjobhistory h
  
•         LEFT JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
  
• WHERE run_status = 1
  
•         AND run_date = CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS INT)
  
• GROUP BY name ,
  
•         run_date
  
• ORDER BY Sum_Duration DESC
  

　　参考资料：
　　http://blog.iyunv.com/dba_huangzj/article/details/8300178