SQL Server作业
SQL Server作业如果有一项工作需要自动执行，比如在每天0点时候执行，或者每隔多长时间执行一次，那么，可以使用Sql server的作业功能。
一、作业分类
创建作业时，往往需要指定作业类别，如果不指定新建作业类别，就会默认为“[未分类(本地)]”，如下截图所示：
二、SQL Server作业的创建实例（以存储过程为例）
1、写好需要执行的存储过程
2、如果sql server代理无法使用，那么需要进行开启
3、新建作业
4、设置作业信息
5、配置好作业后，程序就自动每天执行了
三、常用管理作业SQL
1、查看属于某个数据库的所有作业
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、查看某个作业类别的所有作业
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、查看出错的作业记录
查询那些作业在今天出错（如果要查询历史出错作业，去掉查询时间条件即可）
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) )
查看出错详细信息
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)