SQL Server中找出执行时间过长的作业学习标签:
SQL Server
本文导读:SQL SERVER中作业在执行的过程中,有些时候,部分作业会执行很长时间,我们在排查问题时,可以使用SQL语句找出执行时间过长的作业。
一、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
|
|
来自: 小鱼儿363 > 《sqlserver_job》