当前位置:数据库 > 数据库管理> 正文

SQL Server中找出执行时间过长的作业

时间:2016-6-4类别:数据库

SQL Server中找出执行时间过长的作业

SQL Server中找出执行时间过长的作业

一、SQL Server中查询作业的运行时间

 

  •  
  • SQL 代码   复制
  • 
    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
    ORDER BY Job_Name, h.Step_Id
    
    		
  •  

     

    二、查询今天执行时间大于一分钟的作业

     

  •  
  • SQL 代码   复制
  • 
    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_Date = CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS INT)  --今天执行的作业
        AND h.run_status = 1    --执行成功
        AND h.run_duration > 100 --大于一分钟的作业
    ORDER BY Job_Name, h.Step_Id
    
    		
  •  

    标签:
    上一篇下一篇

    猜您喜欢

    热门推荐