SQL Server Agent

SQL Server Agent Job History

SQL Server Jobs History

Objective:

To find the SQL Server Agent Jobs duration including each step in the job, the below script may be useful. Tested on SQL Server 2016.

Companies SQL Server instance may experience some performance issue due to long running jobs. As such, the primary investigation is to get the duration of jobs from SQL Server Agent. If we identity, any specific jobs running long time, then we may concentrate on that specific job information with a period of time and that history of job duration will give an idea what went wrong. It assists for further investigation.

SELECT 
	SERVERPROPERTY('servername') as [ServerName/SQLInstanceName],
	SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as [ComputerName/NodeName],
	SERVERPROPERTY('IsClustered') as IsClustered ,
	j.name as 'JobName',
	s.step_id as 'Step', -- This will give RunDateTime and Duration in minutes time in each step of the particular job
	s.step_name as 'StepName',
	msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',  -- This gives Date and Time
	((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) AS 'RunDurationMinutes',
	STUFF(STUFF(RIGHT(REPLICATE('0', 6) +  CAST(run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') 'run_time', -- This gives only the time, i.e. Hours when the job ran
    STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(run_duration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') 'run_duration (DD:HH:MM:SS)'
FROM msdb.dbo.sysjobs j
	INNER JOIN msdb.dbo.sysjobsteps s 
ON j.job_id = s.job_id
	INNER JOIN msdb.dbo.sysjobhistory h 
ON s.job_id = h.job_id 
	AND s.step_id = h.step_id 
	AND h.step_id <> 0
WHERE j.enabled = 1   --Only Enabled Jobs
and j.name = 'DTA Purge and Archive (BizTalkDTADb)' -- You need to comment if you want to look for all the jobs.
/*
and msdb.dbo.agent_datetime(run_date, run_time) 
BETWEEN '21/07/2017' and '21/07/2017'  --Uncomment for date range queries
*/
order by JobName, RunDateTime desc
GO

 

Leave a Reply

Your email address will not be published. Required fields are marked *