mssql 作業，mssql

mssql 作業，mssql

1.什麼叫作業？

作業是一系列由 SQL Server Agent按順序執行的指定操作。 一個作業可以執行各種類型的活動，包括運行 Transact-SQL 指令碼、命令提示字元應用程式、Microsoft ActiveX 指令碼、Integration Services 包、Analysis Services 命令和查詢或複製任務。 作業可以運行重複或可計劃的任務，然後它們可以通過生

成警報來自動通知使用者作業狀態，從而極大地簡化了 SQL Server 管理。

2.先看一個常用通過GUI利用作業動態備份資料據庫。

    1、開啟SQL Server Management Studio    2、啟動SQL Server代理    3、點擊作業->新增作業    4、"常規"中輸入作業的名稱    5、建立步驟，類型選T-SQL，在下面的命令中輸入下面語句     DECLARE @strPath NVARCHAR(200)        set @strPath = convert(NVARCHAR(20),getdate(),120)        set @strPath = REPLACE(@strPath, ':' , '.')        set @strPath = 'E:\DATA_db\MSSQL.1\MSSQL\Backup' + @strPath + '.bak'        BACKUP DATABASE [資料庫名] TO DISK = @strPath WITH NOINIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT     6、添加計劃，設定頻率，時間等。基本把以上的流程基本的填寫完，就可以正常作業了。

3.作業系統資料表

    SELECT * FROM msdb.dbo.sysjobs            --儲存將由 SQL Server Agent執行的各個預定作業的資訊　　SELECT * FROM msdb.dbo.sysjobschedules    --包含將由 SQL Server Agent執行的作業的計劃資訊　　SELECT * FROM msdb.dbo.sysjobactivity;    --記錄當前 SQL Server Agent作業活動和狀態　　SELECT * FROM msdb.dbo.sysjobservers      --儲存特定作業與一個或多個目標伺服器的關聯或關係　　SELECT * FROM msdb.dbo.sysjobsteps;       --包含 SQL Server Agent要執行的作業中的各個步驟的資訊　　SELECT * FROM msdb.dbo.sysjobstepslogs;   --包含所有 SQL Server Agent作業步驟的作業步驟日誌　　SELECT * FROM msdb.dbo.sysjobs_view;      --　　SELECT * FROM msdb.dbo.sysjobhistory      --包含有關 SQL Server Agent執行預定作業的資訊　　SELECT * FROM msdb.dbo.syscategories      --包含由 SQL Server Management Studio 用來組織作業、警報和操作員的類別

4.建立作業

　建立作業的步驟一般如下所示：　　執行 sp_add_job 來建立作業。　　執行 sp_add_jobstep 來建立一個或多個作業步驟。　　執行 sp_add_schedule 來建立計劃。　　執行 sp_attach_schedule 將計劃附加到作業。　　執行 sp_add_jobserver 來設定作業的伺服器。　　本地作業是由本地 SQL Server Agent進行緩衝的。因此，任何修改都會隱式強制 SQL Server Agent重新緩衝該作業。由於直到調用 sp_add_jobserver 時，SQL Server Agent才緩衝作業，因此最後調用 sp_add_jobserver 將更為有效。

啟動作業

        1：通過SSMS工具啟動作業[略]

        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工具停作業[略]

        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工具啟用作業[略]

        2：通過SQL命令禁用作業

文法:

列子：EXEC msdb.dbo.sp_update_job

    @job_name = N'JOB_CYCLE_ERRORLOG',

    @enabled = 0 ;  --0 禁用作業、  1啟用作業

GO

刪除作業

1：通過SSMS工具刪除作業[略]

2：通過SQL命令刪除作業

例子：

EXEC msdb.dbo.sp_delete_job  @job_name = 'JOB_CYCLE_ERRORLOG';

    

5.查看作業的T-SQL語句

　　--1:查看屬於某個資料庫的所有作業。　　SELECT job.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 job　　WHERE   job_id IN( SELECTjob_id　　                  FROM    msdb.dbo.sysjobsteps　　                  WHERE   database_name = 'DataBaseName' )　　 --2:查看某個作業類別的所有作業　　SELECT　　job.name             AS Job_Name       ,　　         job.description      AS Job_Description ,　　         job.date_created     AS Date_Created    ,　　         job.date_modified    AS Date_Modified   ,　　         type .name             AS Job_Class　　FROM  msdb.dbo.sysjobs job　　      LEFT JOIN msdb.dbo.syscategories type ON job.category_id = type .category_id　　 WHERE type.name = '[Uncategorized (Local)]'　　 　　--3:查看禁用/啟用的作業　　SELECT * FROM msdb.dbo.sysjobs WHERE  enabled=0    --0：禁用 1：為啟用　　 　　--4:查看出錯的作業記錄　　 　　--4.1：查詢那些作業在今天出錯（如果要查詢曆史出錯作業，去掉查詢時間條件即可）　　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：查看出錯詳細資料　　SELECT job.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 job ON h.job_id = job.job_id　　WHERE run_status = 0　　      AND run_date = CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS INT)　　 　　--5：查看作業的執行時間:　　--5.1:查看當天成功執行的作業的時間（查看的是作業Step資訊）　　SELECT job.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 job ON h.job_id = job.job_id　　 WHERE run_status = 1　　      AND run_date = CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS INT)　　      ORDER BY run_duration DESC　　 　　--5.2：查詢每個作業的執行時間、按執行時間降序　　SELECT  job.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 job ON h.job_id = job.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