原 MSSQL作业JOB介绍

发布日期 2023年11月7日 · 已更新 2024年7月11日

0 286 3

👉 本文共约1698个字，系统预计阅读时间或需7分钟。

简介
查询所有作业（用这个）
查询作业基本信息和作业执行情况
作业最后执行情况
查看每个作业步骤基本信息
查看每个作业步骤执行情况
清除job日志
定时删除
SQL清理
界面删除
使用 sp_syspolicy_purge_history
删除特定的不需要的日志
如何判断job是否正在执行
获取job执行的命令
参考

简介

SQL Server 作业是 SQL Server 数据库管理系统中的一种自动化任务调度工具，用于执行一系列预定义的任务或操作。作业可以包括诸如备份、数据清理、维护、ETL（抽取、转换和加载）过程等一系列操作，可以根据计划或事件来执行。

查询所有作业（用这个）


SELECT sj.name AS [Name],
   sh.step_id,
   sh.step_name AS [StepName],
   DATETIMEFROMPARTS(
       LEFT(padded_run_date, 4),         -- year
       SUBSTRING(padded_run_date, 5, 2), -- month
       RIGHT(padded_run_date, 2),        -- day
       LEFT(padded_run_time, 2),         -- hour
       SUBSTRING(padded_run_time, 3, 2), -- minute
       RIGHT(padded_run_time, 2),        -- second
       0) AS [LastRunDateTime],          -- millisecond
   CASE
       WHEN sh.run_duration > 235959
           THEN CAST((CAST(LEFT(CAST(sh.run_duration AS VARCHAR), LEN(CAST(sh.run_duration AS VARCHAR)) - 4) AS INT) / 24) AS VARCHAR) + '.' + RIGHT('00' + CAST(CAST(LEFT(CAST(sh.run_duration AS VARCHAR), LEN(CAST(sh.run_duration AS VARCHAR)) - 4) AS INT) % 24 AS VARCHAR), 2) + ':' + STUFF(CAST(RIGHT(CAST(sh.run_duration AS VARCHAR), 4) AS VARCHAR(6)), 3, 0, ':')
       ELSE STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(sh.run_duration AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
       END AS [LastRunDuration (d.HH:MM:SS)],
       sh.run_status,
       sh.message,
       sh.server,
       jstep.command
FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.sysjobhistory sh
   ON sj.job_id = sh.job_id
left JOIN  [msdb].[dbo].[sysjobsteps] AS [jstep]
   ON sj.job_id = jstep.job_id and  sh.step_id=jstep.step_id
CROSS APPLY (
   SELECT RIGHT('000000' + CAST(sh.run_time AS VARCHAR(6)), 6),
       RIGHT('00000000' + CAST(sh.run_date AS VARCHAR(8)), 8)
   ) AS shp(padded_run_time, padded_run_date)
 where name='ATFACT_JOB'
--  and sh.run_duration>='1000'  -- 执行时长大于10分钟
 -- and instance_id>=557203
 order by instance_id desc
GO

SELECT sj.name AS [Name],

sh.step_id,

sh.step_name AS [StepName],

DATETIMEFROMPARTS(

LEFT(padded_run_date, 4), -- year

SUBSTRING(padded_run_date, 5, 2), -- month

RIGHT(padded_run_date, 2), -- day

LEFT(padded_run_time, 2), -- hour

SUBSTRING(padded_run_time, 3, 2), -- minute

RIGHT(padded_run_time, 2), -- second

0) AS [LastRunDateTime], -- millisecond

CASE

WHEN sh.run_duration > 235959

THEN CAST((CAST(LEFT(CAST(sh.run_duration AS VARCHAR), LEN(CAST(sh.run_duration AS VARCHAR)) - 4) AS INT) / 24) AS VARCHAR) + '.' + RIGHT('00' + CAST(CAST(LEFT(CAST(sh.run_duration AS VARCHAR), LEN(CAST(sh.run_duration AS VARCHAR)) - 4) AS INT) % 24 AS VARCHAR), 2) + ':' + STUFF(CAST(RIGHT(CAST(sh.run_duration AS VARCHAR), 4) AS VARCHAR(6)), 3, 0, ':')

ELSE STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(sh.run_duration AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')

END AS [LastRunDuration (d.HH:MM:SS)],

sh.run_status,

sh.message,

sh.server,

jstep.command

FROM msdb.dbo.sysjobs sj

INNER JOIN msdb.dbo.sysjobhistory sh

ON sj.job_id = sh.job_id

left JOIN [msdb].[dbo].[sysjobsteps] AS [jstep]

ON sj.job_id = jstep.job_id and sh.step_id=jstep.step_id

CROSS APPLY (

SELECT RIGHT('000000' + CAST(sh.run_time AS VARCHAR(6)), 6),

RIGHT('00000000' + CAST(sh.run_date AS VARCHAR(8)), 8)

) AS shp(padded_run_time, padded_run_date)

where name='ATFACT_JOB'

-- and sh.run_duration>='1000' -- 执行时长大于10分钟

-- and instance_id>=557203

order by instance_id desc

分步：


SELECT * FROM msdb.dbo.sysjobs d where d.name IN ('ATFACT_JOB','CDC_HIS_ZY');

SELECT * FROM msdb.dbo.sysjobhistory a
 where job_id IN ('65AE97BA-87C5-4B07-90C8-6615C8F8829F','E48BF8E1-60B5-4174-AC53-77FBF1DF03CA') 
 -- and a.run_duration>=10*60*1000
 order by instance_id desc

SELECT * FROM msdb.dbo.sysjobs d where d.name IN ('ATFACT_JOB','CDC_HIS_ZY');

SELECT * FROM msdb.dbo.sysjobhistory a

where job_id IN ('65AE97BA-87C5-4B07-90C8-6615C8F8829F','E48BF8E1-60B5-4174-AC53-77FBF1DF03CA')

-- and a.run_duration>=10*60*1000

order by instance_id desc

查询作业基本信息和作业执行情况

SELECT
    [jop].[job_id] AS '作业唯一标识符'
   ,[jop].[name] AS '作业名称'
   ,[dp].[name] AS '作业创建者'
   ,[cat].[name] AS '作业类别'
   ,[jop].[description] AS '作业描述'
   , CASE [jop].[enabled]
        WHEN 1 THEN '是'
        WHEN 0 THEN '否'
      END AS '是否启用'
   ,[jop].[date_created] AS '作业创建日期'
   ,[jop].[date_modified] AS '作业最后修改日期'
   ,[sv].[name] AS '作业运行服务器名称'
   ,[step].[step_id] AS '作业起始步骤'
   ,[step].[step_name] AS '步骤名称'
   , CASE
        WHEN [sch].[schedule_uid] IS NULL THEN '否'
          ELSE '是'
      END AS '是否分布式作业'
   ,[sch].[schedule_uid] AS '作业计划的唯一标识符'
   ,[sch].[name] AS '作业计划的用户定义名称'
   , CASE [jop].[delete_level]
        WHEN 0 THEN '不删除'
        WHEN 1 THEN '成功后删除'
        WHEN 2 THEN '失败后删除'
        WHEN 3 THEN '完成后删除'
      END AS '作业完成删除选项'
FROM [msdb].[dbo].[sysjobs] AS [jop]
LEFT JOIN [msdb].[sys].[servers] AS [sv]
         ON [jop].[originating_server_id] = [sv].[server_id]
LEFT JOIN [msdb].[dbo].[syscategories] AS [cat]
         ON [jop].[category_id] = [cat].[category_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [step]
         ON [jop].[job_id] = [step].[job_id]
            AND [jop].[start_step_id] = [step].[step_id]
LEFT JOIN [msdb].[sys].[database_principals] AS [dp]
         ON [jop].[owner_sid] = [dp].[sid]
LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [jsch]
         ON [jop].[job_id] = [jsch].[job_id]
LEFT JOIN [msdb].[dbo].[sysschedules] AS [sch]
         ON [jsch].[schedule_id] = [sch].[schedule_id]
ORDER BY [jop].[name]

SELECT

[jop].[job_id] AS '作业唯一标识符'

,[jop].[name] AS '作业名称'

,[dp].[name] AS '作业创建者'

,[cat].[name] AS '作业类别'

,[jop].[description] AS '作业描述'

, CASE [jop].[enabled]

WHEN 1 THEN '是'

WHEN 0 THEN '否'

END AS '是否启用'

,[jop].[date_created] AS '作业创建日期'

,[jop].[date_modified] AS '作业最后修改日期'

,[sv].[name] AS '作业运行服务器名称'

,[step].[step_id] AS '作业起始步骤'

,[step].[step_name] AS '步骤名称'

, CASE

WHEN [sch].[schedule_uid] IS NULL THEN '否'

ELSE '是'

END AS '是否分布式作业'

,[sch].[schedule_uid] AS '作业计划的唯一标识符'

,[sch].[name] AS '作业计划的用户定义名称'

, CASE [jop].[delete_level]

WHEN 0 THEN '不删除'

WHEN 1 THEN '成功后删除'

WHEN 2 THEN '失败后删除'

WHEN 3 THEN '完成后删除'

END AS '作业完成删除选项'

FROM [msdb].[dbo].[sysjobs] AS [jop]

LEFT JOIN [msdb].[sys].[servers] AS [sv]

ON [jop].[originating_server_id] = [sv].[server_id]

LEFT JOIN [msdb].[dbo].[syscategories] AS [cat]

ON [jop].[category_id] = [cat].[category_id]

LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [step]

ON [jop].[job_id] = [step].[job_id]

AND [jop].[start_step_id] = [step].[step_id]

LEFT JOIN [msdb].[sys].[database_principals] AS [dp]

ON [jop].[owner_sid] = [dp].[sid]

LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [jsch]

ON [jop].[job_id] = [jsch].[job_id]

LEFT JOIN [msdb].[dbo].[sysschedules] AS [sch]

ON [jsch].[schedule_id] = [sch].[schedule_id]

ORDER BY [jop].[name]

作业最后执行情况

SELECT
    [job].[job_id] AS '作业唯一标示符'
   ,[job].[name] AS '作业名称'
   , CASE WHEN [jobh].[run_date] IS NULL
               OR [jobh].[run_time] IS NULL THEN NULL
          ELSE CAST ( CAST ([jobh].[run_date] AS CHAR ( 8 )) + ' '
               + STUFF(STUFF( RIGHT ( '000000'
                                   + CAST ([jobh].[run_time] AS VARCHAR ( 6 )), 6 ),
                             3 , 0 , ':' ), 6 , 0 , ':' ) AS DATETIME)
     END AS '最后执行时间'
   , CASE [jobh].[run_status]
      WHEN 0 THEN '失败'
      WHEN 1 THEN '成功'
      WHEN 2 THEN '重试'
      WHEN 3 THEN '取消'
      WHEN 4 THEN '正在运行'
     END AS '最后执行状态'
   ,STUFF(STUFF( RIGHT ( '000000' + CAST ([jobh].[run_duration] AS VARCHAR ( 6 )), 6 ),
                 3 , 0 , ':' ), 6 , 0 , ':' ) AS '最后运行持续时间'
   ,[jobh].[message] AS '最后运行状态信息'
   , CASE [jsch].[NextRunDate]
      WHEN 0 THEN NULL
       ELSE CAST ( CAST ([jsch].[NextRunDate] AS CHAR ( 8 )) + ' '
            + STUFF(STUFF( RIGHT ( '000000'
                                + CAST ([jsch].[NextRunTime] AS VARCHAR ( 6 )),
                                6 ), 3 , 0 , ':' ), 6 , 0 , ':' ) AS DATETIME)
     END AS '下次运行时间'
FROM [msdb].[dbo].[sysjobs] AS [job]
LEFT JOIN (
             SELECT
                [job_id]
               , MIN ([next_run_date]) AS [NextRunDate]
               , MIN ([next_run_time]) AS [NextRunTime]
             FROM [msdb].[dbo].[sysjobschedules]
             GROUP BY [job_id]
          ) AS [jsch]
         ON [job].[job_id] = [jsch].[job_id]
LEFT JOIN (
             SELECT
                [job_id]
               ,[run_date]
               ,[run_time]
               ,[run_status]
               ,[run_duration]
               ,[message]
               ,ROW_NUMBER() OVER ( PARTITION BY [job_id] ORDER BY [run_date] DESC , [run_time] DESC ) AS RowNumber
             FROM [msdb].[dbo].[sysjobhistory]
             WHERE [step_id] = 0
          ) AS [jobh]
     ON [job].[job_id] = [jobh].[job_id]
        AND [jobh].[RowNumber] = 1
ORDER BY [job].[name]

SELECT

[job].[job_id] AS '作业唯一标示符'

,[job].[name] AS '作业名称'

, CASE WHEN [jobh].[run_date] IS NULL

OR [jobh].[run_time] IS NULL THEN NULL

ELSE CAST ( CAST ([jobh].[run_date] AS CHAR ( 8 )) + ' '

+ STUFF(STUFF( RIGHT ( '000000'

+ CAST ([jobh].[run_time] AS VARCHAR ( 6 )), 6 ),

3 , 0 , ':' ), 6 , 0 , ':' ) AS DATETIME)

END AS '最后执行时间'

, CASE [jobh].[run_status]

WHEN 0 THEN '失败'

WHEN 1 THEN '成功'

WHEN 2 THEN '重试'

WHEN 3 THEN '取消'

WHEN 4 THEN '正在运行'

END AS '最后执行状态'

,STUFF(STUFF( RIGHT ( '000000' + CAST ([jobh].[run_duration] AS VARCHAR ( 6 )), 6 ),

3 , 0 , ':' ), 6 , 0 , ':' ) AS '最后运行持续时间'

,[jobh].[message] AS '最后运行状态信息'

, CASE [jsch].[NextRunDate]

WHEN 0 THEN NULL

ELSE CAST ( CAST ([jsch].[NextRunDate] AS CHAR ( 8 )) + ' '

+ STUFF(STUFF( RIGHT ( '000000'

+ CAST ([jsch].[NextRunTime] AS VARCHAR ( 6 )),

6 ), 3 , 0 , ':' ), 6 , 0 , ':' ) AS DATETIME)

END AS '下次运行时间'

FROM [msdb].[dbo].[sysjobs] AS [job]

LEFT JOIN (

SELECT

[job_id]

, MIN ([next_run_date]) AS [NextRunDate]

, MIN ([next_run_time]) AS [NextRunTime]

FROM [msdb].[dbo].[sysjobschedules]

GROUP BY [job_id]

) AS [jsch]

ON [job].[job_id] = [jsch].[job_id]

LEFT JOIN (

SELECT

[job_id]

,[run_date]

,[run_time]

,[run_status]

,[run_duration]

,[message]

,ROW_NUMBER() OVER ( PARTITION BY [job_id] ORDER BY [run_date] DESC , [run_time] DESC ) AS RowNumber

FROM [msdb].[dbo].[sysjobhistory]

WHERE [step_id] = 0

) AS [jobh]

ON [job].[job_id] = [jobh].[job_id]

AND [jobh].[RowNumber] = 1

ORDER BY [job].[name]

查看每个作业步骤基本信息

SELECT
    [job].[job_id] AS '作业唯一标识符'
   ,[job].[name] AS '作业名称'
   ,[jstep].[step_uid] AS '步骤唯一标识符'
   ,[jstep].[step_id] AS '步骤编号'
   ,[jstep].[step_name] AS '步骤名称'
   ,CASE [jstep].[subsystem]
      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 [jstep].[subsystem]
    END AS '作业步骤类型'
   ,CASE
        WHEN [px].[name] IS NULL THEN 'SQL SERVER代理服务账户'
        ELSE [px].[name]
    END AS '步骤运行账户'
   ,[jstep].[database_name] AS '执行数据库名'
   ,[jstep].[command] AS '执行命令'
   ,CASE [jstep].[on_success_action]
      WHEN 1 THEN '退出报表成功的作业'
      WHEN 2 THEN '退出报告失败的作业'
      WHEN 3 THEN '转到下一步'
      WHEN 4
      THEN '转到步骤: '
           + QUOTENAME(CAST([jstep].[on_success_step_id] AS VARCHAR(3))) + ' '
           + [sOSSTP].[step_name]
    END AS '执行成功后操作'
   ,[jstep].[retry_attempts] AS '失败时的重试次数'
   ,[jstep].[retry_interval] AS '重试间的等待时间(分钟)'
   ,CASE [jstep].[on_fail_action]
      WHEN 1 THEN '退出报告成功的作业'
      WHEN 2 THEN '退出报告失败的作业'
      WHEN 3 THEN '转到下一步'
      WHEN 4
      THEN '转到步骤: '
           + QUOTENAME(CAST([jstep].[on_fail_step_id] AS VARCHAR(3))) + ' '
           + [sOFSTP].[step_name]
    END AS '执行失败后操作'
FROM [msdb].[dbo].[sysjobsteps] AS [jstep]
INNER JOIN [msdb].[dbo].[sysjobs] AS [job]
        ON [jstep].[job_id] = [job].[job_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP]
        ON [jstep].[job_id] = [sOSSTP].[job_id]
           AND [jstep].[on_success_step_id] = [sOSSTP].[step_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP]
        ON [jstep].[job_id] = [sOFSTP].[job_id]
           AND [jstep].[on_fail_step_id] = [sOFSTP].[step_id]
LEFT JOIN [msdb].[dbo].[sysproxies] AS [px]--代理账户信息
        ON [jstep].[proxy_id] = [px].[proxy_id]
ORDER BY [job].[name], [jstep].[step_id]

SELECT

[job].[job_id] AS '作业唯一标识符'

,[job].[name] AS '作业名称'

,[jstep].[step_uid] AS '步骤唯一标识符'

,[jstep].[step_id] AS '步骤编号'

,[jstep].[step_name] AS '步骤名称'

,CASE [jstep].[subsystem]

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 [jstep].[subsystem]

END AS '作业步骤类型'

,CASE

WHEN [px].[name] IS NULL THEN 'SQL SERVER代理服务账户'

ELSE [px].[name]

END AS '步骤运行账户'

,[jstep].[database_name] AS '执行数据库名'

,[jstep].[command] AS '执行命令'

,CASE [jstep].[on_success_action]

WHEN 1 THEN '退出报表成功的作业'

WHEN 2 THEN '退出报告失败的作业'

WHEN 3 THEN '转到下一步'

WHEN 4

THEN '转到步骤: '

+ QUOTENAME(CAST([jstep].[on_success_step_id] AS VARCHAR(3))) + ' '

+ [sOSSTP].[step_name]

END AS '执行成功后操作'

,[jstep].[retry_attempts] AS '失败时的重试次数'

,[jstep].[retry_interval] AS '重试间的等待时间(分钟)'

,CASE [jstep].[on_fail_action]

WHEN 1 THEN '退出报告成功的作业'

WHEN 2 THEN '退出报告失败的作业'

WHEN 3 THEN '转到下一步'

WHEN 4

THEN '转到步骤: '

+ QUOTENAME(CAST([jstep].[on_fail_step_id] AS VARCHAR(3))) + ' '

+ [sOFSTP].[step_name]

END AS '执行失败后操作'

FROM [msdb].[dbo].[sysjobsteps] AS [jstep]

INNER JOIN [msdb].[dbo].[sysjobs] AS [job]

ON [jstep].[job_id] = [job].[job_id]

LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP]

ON [jstep].[job_id] = [sOSSTP].[job_id]

AND [jstep].[on_success_step_id] = [sOSSTP].[step_id]

LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP]

ON [jstep].[job_id] = [sOFSTP].[job_id]

AND [jstep].[on_fail_step_id] = [sOFSTP].[step_id]

LEFT JOIN [msdb].[dbo].[sysproxies] AS [px]--代理账户信息

ON [jstep].[proxy_id] = [px].[proxy_id]

ORDER BY [job].[name], [jstep].[step_id]

查看每个作业步骤执行情况

SELECT
    [job].[job_id] AS '作业唯一标识符'
   ,[job].[name] AS '作业名称'
   ,[jstep].[step_uid] AS '作业步骤唯一标识符'
   ,[jstep].[step_id] AS '步骤编号'
   ,[jstep].[step_name] AS '步骤名称'
   ,CASE [jstep].[last_run_outcome]
      WHEN 0 THEN '失败'
      WHEN 1 THEN '成功'
      WHEN 2 THEN '重试'
      WHEN 3 THEN '取消'
      WHEN 5 THEN '未知'
    END AS '上次运行状态'
   ,STUFF(STUFF(RIGHT('000000'
                      + CAST([jstep].[last_run_duration] AS VARCHAR(6)), 6), 3,
                0, ':'), 6, 0, ':') AS [LastRunDuration (HH:MM:SS)]
   ,[jstep].[last_run_retries] AS '上次运行重复执行次数'
   ,CASE [jstep].[last_run_date]
      WHEN 0 THEN NULL
      ELSE CAST(CAST([jstep].[last_run_date] AS CHAR(8)) + ' '
           + STUFF(STUFF(RIGHT('000000'
                               + CAST([jstep].[last_run_time] AS VARCHAR(6)),
                               6), 3, 0, ':'), 6, 0, ':') AS DATETIME)
    END AS '上次运行时间'
FROM [msdb].[dbo].[sysjobsteps] AS [jstep]
INNER JOIN [msdb].[dbo].[sysjobs] AS [job]
        ON [jstep].[job_id] = [job].[job_id]
ORDER BY [job].[name], [jstep].[step_id]

SELECT

[job].[job_id] AS '作业唯一标识符'

,[job].[name] AS '作业名称'

,[jstep].[step_uid] AS '作业步骤唯一标识符'

,[jstep].[step_id] AS '步骤编号'

,[jstep].[step_name] AS '步骤名称'

,CASE [jstep].[last_run_outcome]

WHEN 0 THEN '失败'

WHEN 1 THEN '成功'

WHEN 2 THEN '重试'

WHEN 3 THEN '取消'

WHEN 5 THEN '未知'

END AS '上次运行状态'

,STUFF(STUFF(RIGHT('000000'

+ CAST([jstep].[last_run_duration] AS VARCHAR(6)), 6), 3,

0, ':'), 6, 0, ':') AS [LastRunDuration (HH:MM:SS)]

,[jstep].[last_run_retries] AS '上次运行重复执行次数'

,CASE [jstep].[last_run_date]

WHEN 0 THEN NULL

ELSE CAST(CAST([jstep].[last_run_date] AS CHAR(8)) + ' '

+ STUFF(STUFF(RIGHT('000000'

+ CAST([jstep].[last_run_time] AS VARCHAR(6)),

6), 3, 0, ':'), 6, 0, ':') AS DATETIME)

END AS '上次运行时间'

FROM [msdb].[dbo].[sysjobsteps] AS [jstep]

INNER JOIN [msdb].[dbo].[sysjobs] AS [job]

ON [jstep].[job_id] = [job].[job_id]

ORDER BY [job].[name], [jstep].[step_id]

清除job日志

定时删除

SQL清理

-- example removes the history for a job named NightlyBackups.  
USE msdb ;  
GO  

EXEC dbo.sp_purge_jobhistory  
    @job_name = N'NightlyBackups' ;  
GO

-- example removes the history for a job named NightlyBackups.

USE msdb ;

EXEC dbo.sp_purge_jobhistory

@job_name = N'NightlyBackups' ;

界面删除

在 “对象资源管理器” 中，连接到 SQL Server 数据库引擎的实例，然后展开该实例。
展开 “SQL Server 代理”，再展开 “作业”。
右键单击某个作业，再单击 “查看历史记录”。
在 “日志文件查看器”中，选择要清除其历史记录的作业，再执行下列操作：
- 单击 “删除”，再单击 “删除历史记录” 对话框中的 “删除所有历史记录” 。可以删除所有作业历史记录，或者仅删除早于指定日期的历史记录。若要删除所有作业历史记录，请单击 “删除所有历史记录”。如果只删除较早的作业历史记录日志，请单击 “删除以下时间之前的历史记录”，然后指定日期。
- 单击 “作业状态” （如果要清除多服务器作业的历史记录日志）。单击 “作业”，单击某个作业名，再单击 “查看远程作业历史记录”。
单击 “删除” 。

使用 sp_syspolicy_purge_history


EXEC msdb.dbo.sp_syspolicy_purge_history;
GO

EXEC msdb.dbo.sp_syspolicy_purge_history;

删除特定的不需要的日志


-- 删除不需要的日志
delete msdb.dbo.sysjobhistory where job_id ='3291D361-C7CA-49E8-9FB3-AF6FFF6DA09B'
and run_date='20240205'
and instance_id>=6206551 and instance_id<=6210698;

-- 删除不需要的日志

delete msdb.dbo.sysjobhistory where job_id ='3291D361-C7CA-49E8-9FB3-AF6FFF6DA09B'

and run_date='20240205'

and instance_id>=6206551 and instance_id<=6210698;

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

获取验证码：请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

打赏赞(3)

标签：原创 MSSQL SQL Server 小麦苗常用 job 作业

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

1、声明：本网站有部分文章整合或转载自网络，文章著作权归属原作者或原团队；若有侵权，深表歉意，请联系本站站长进行处理，谢谢！！！
2、本站提供数据库的培训和考证业务，培训包含但不限于Oracle、MySQL、PostgreSQL、SQL Server、国产数据库等，考证包括但不限于Oracle OCP、Oracle OCM、MySQL OCP、PGCA、PGCE、PGCM等，私聊QQ646634621或微信dbaup66，非诚勿扰，谢谢！！！
3、麦老师新建了《西安DBA数据架构师》微信群，有志同道合的朋友可以私聊我入群，我们一起聊技术，聊生活，空了可以喝酒约饭。
4、为了方便大家更有针对性的讨论学习数据库，故新建数据库分类群，包括Oracle群、MySQL群、SQL Server群、PG&GP群、国产&信创群。志同道合的朋友可以入群聊技术聊人生，捣乱的勿进，喷子勿进，极端人士勿进，谢谢。

原 MSSQL作业JOB介绍

简介

查询所有作业（用这个）

查询作业基本信息和作业执行情况

作业最后执行情况

查看每个作业步骤基本信息

查看每个作业步骤执行情况

清除job日志

定时删除

SQL清理

界面删除

使用 sp_syspolicy_purge_history

删除特定的不需要的日志

相关文章

您可能还喜欢...

发表回复取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

2024 年 9 月
一	二	三	四	五	六	日
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30

原 MSSQL作业JOB介绍

简介

查询所有作业（用这个）

查询作业基本信息和作业执行情况

作业最后执行情况

查看每个作业步骤基本信息

查看每个作业步骤执行情况

清除job日志

定时删除

SQL清理

界面删除

使用 sp_syspolicy_purge_history

删除特定的不需要的日志

相关文章

您可能还喜欢...

【PG】小麦苗PGCM第1期证书邮寄

Greenplum查询用户或角色

openGauss数据库巡检脚本

发表回复 取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

发表回复取消回复