Category Archives: Job Information

Duration of SQL Jobs – steps

The follownig list the duration of the steps of jobs.

For an overall duration, see http://www.cookingsql.com/2017/02/duration-of-sql-jobs/

 


SELECT TOP (100) PERCENT
 msdb.dbo.sysjobs.name AS JobName,
 msdb.dbo.sysjobsteps.step_id AS Step,
 msdb.dbo.agent_datetime(msdb.dbo.sysjobhistory.run_date,
 msdb.dbo.sysjobhistory.run_time) AS RunDateTime,
 (msdb.dbo.sysjobhistory.run_duration / 10000 * 3600 + msdb.dbo.sysjobhistory.run_duration / 100 % 100 * 60) 
                      + msdb.dbo.sysjobhistory.run_duration % 100 AS RunDurationSeconds,
 msdb.dbo.sysjobsteps.step_name AS StepName,
 msdb.dbo.sysjobsteps.command AS StepCommand, 
 msdb.dbo.sysjobsteps.subsystem AS StepType, 
 msdb.dbo.sysjobsteps.database_name AS DBName, 
 CASE SysJobSteps.last_run_outcome 
		WHEN 0 THEN 'Failed' 
		WHEN 1 THEN 'Succeeded' 
		ELSE 'Other' END AS StepResult

FROM         msdb.dbo.sysjobs 
INNER JOIN msdb.dbo.sysjobsteps ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobsteps.job_id 
LEFT OUTER JOIN msdb.dbo.sysjobhistory ON msdb.dbo.sysjobsteps.job_id = msdb.dbo.sysjobhistory.job_id 
								AND msdb.dbo.sysjobsteps.step_id = msdb.dbo.sysjobhistory.step_id 
								AND msdb.dbo.sysjobhistory.step_id <> 0
WHERE   
    msdb.dbo.sysjobs.enabled = 1
AND (msdb.dbo.sysjobs.name NOT LIKE '%Backups.%') 
AND (msdb.dbo.sysjobs.name NOT LIKE 'syspolicy%') 
AND (msdb.dbo.agent_datetime(msdb.dbo.sysjobhistory.run_date, 0) = DATEADD(dd, - 1, CAST(GETDATE() AS date)))

ORDER BY RunDateTime


 

 

SQL Agent Job Details

Detailed view of the jobs

 

 


/*
Job details, including steps, and commands
*/

SELECT
	  [sJOB].[name] AS [JobName]
    , [sJSTP].[step_id] AS [StepNo]
    , [sJSTP].[step_name] AS [StepName]
    , CASE [sJSTP].[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 sJSTP.subsystem
      END AS [StepType]
    , [sPROX].[name] AS [RunAs]
    , [sJSTP].[database_name] AS [Database]
    , [sJSTP].[command] AS [ExecutableCommand]
    , CASE [sJSTP].[on_success_action]
        WHEN 1 THEN 'Quit the job reporting success'
        WHEN 2 THEN 'Quit the job reporting failure'
        WHEN 3 THEN 'Go to the next step'
        WHEN 4 THEN 'Go to Step: ' 
                    + QUOTENAME(CAST([sJSTP].[on_success_step_id] AS VARCHAR(3))) 
                    + ' ' 
                    + [sOSSTP].[step_name]
      END AS [OnSuccessAction]
    , [sJSTP].[retry_attempts] AS [RetryAttempts]
    , [sJSTP].[retry_interval] AS [RetryInterval (Minutes)]
    , CASE [sJSTP].[on_fail_action]
        WHEN 1 THEN 'Quit the job reporting success'
        WHEN 2 THEN 'Quit the job reporting failure'
        WHEN 3 THEN 'Go to the next step'
        WHEN 4 THEN 'Go to Step: ' 
                    + QUOTENAME(CAST([sJSTP].[on_fail_step_id] AS VARCHAR(3))) 
                    + ' ' 
                    + [sOFSTP].[step_name]
      END AS [OnFailureAction]
FROM
    [msdb].[dbo].[sysjobsteps] AS [sJSTP]
    INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB]
        ON [sJSTP].[job_id] = [sJOB].[job_id]
    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP]
        ON [sJSTP].[job_id] = [sOSSTP].[job_id]
        AND [sJSTP].[on_success_step_id] = [sOSSTP].[step_id]
    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP]
        ON [sJSTP].[job_id] = [sOFSTP].[job_id]
        AND [sJSTP].[on_fail_step_id] = [sOFSTP].[step_id]
    LEFT JOIN [msdb].[dbo].[sysproxies] AS [sPROX]
        ON [sJSTP].[proxy_id] = [sPROX].[proxy_id]

 

SQL Agent Jobs and their Schedules

The following query lists jobs and their schedules

 


/*
Basic Job info, including schedules

*/

SELECT	 [JobName] = [jobs].[name]
		,[Category] = [categories].[name]
		,[Owner] = SUSER_SNAME([jobs].[owner_sid])
		,[Enabled] = CASE [jobs].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END
		,[Scheduled] = CASE [schedule].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END
		--,[Description] = [jobs].[description]
		,[Schedule_Name] = [schedule].[name]
		,[Occurs] = 
				CASE [schedule].[freq_type]
					WHEN   1 THEN 'Once'
					WHEN   4 THEN 'Daily'
					WHEN   8 THEN 'Weekly'
					WHEN  16 THEN 'Monthly'
					WHEN  32 THEN 'Monthly relative'
					WHEN  64 THEN 'When SQL Server Agent starts'
					WHEN 128 THEN 'Start whenever the CPU(s) become idle' 
					ELSE ''
				END
		,[Occurs_detail] = 
				CASE [schedule].[freq_type]
					WHEN   1 THEN 'O'
					WHEN   4 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' day(s)'
					WHEN   8 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' weeks(s) on ' + 
						LEFT(
							CASE WHEN [schedule].[freq_interval] &  1 =  1 THEN 'Sunday, '    ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] &  2 =  2 THEN 'Monday, '    ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] &  4 =  4 THEN 'Tuesday, '   ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] &  8 =  8 THEN 'Wednesday, ' ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, '  ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, '    ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, '  ELSE '' END , 
							LEN(
								CASE WHEN [schedule].[freq_interval] &  1 =  1 THEN 'Sunday, '    ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] &  2 =  2 THEN 'Monday, '    ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] &  4 =  4 THEN 'Tuesday, '   ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] &  8 =  8 THEN 'Wednesday, ' ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, '  ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, '    ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, '  ELSE '' END 
							) - 1
						)
					WHEN  16 THEN 'Day ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)'
					WHEN  32 THEN 'The ' + 
							CASE [schedule].[freq_relative_interval]
								WHEN  1 THEN 'First'
								WHEN  2 THEN 'Second'
								WHEN  4 THEN 'Third'
								WHEN  8 THEN 'Fourth'
								WHEN 16 THEN 'Last' 
							END +
							CASE [schedule].[freq_interval]
								WHEN  1 THEN ' Sunday'
								WHEN  2 THEN ' Monday'
								WHEN  3 THEN ' Tuesday'
								WHEN  4 THEN ' Wednesday'
								WHEN  5 THEN ' Thursday'
								WHEN  6 THEN ' Friday'
								WHEN  7 THEN ' Saturday'
								WHEN  8 THEN ' Day'
								WHEN  9 THEN ' Weekday'
								WHEN 10 THEN ' Weekend Day' 
							END + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)' 
					ELSE ''
				END
		,[Frequency] = 
				CASE [schedule].[freq_subday_type]
					WHEN 1 THEN 'Occurs once at ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':')
					WHEN 2 THEN 'Occurs every ' + 
								CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Seconds(s) between ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
					WHEN 4 THEN 'Occurs every ' + 
								CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Minute(s) between ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
					WHEN 8 THEN 'Occurs every ' + 
								CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Hour(s) between ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
					ELSE ''
				END
		,[AvgDurationInSec] = CONVERT(DECIMAL(10, 2), [jobhistory].[AvgDuration])
		,[Next_Run_Date] = 
				CASE [jobschedule].[next_run_date]
					WHEN 0 THEN CONVERT(DATETIME, '1900/1/1')
					ELSE CONVERT(DATETIME, CONVERT(CHAR(8), [jobschedule].[next_run_date], 112) + ' ' + 
						 STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [jobschedule].[next_run_time]), 6), 5, 0, ':'), 3, 0, ':'))
				END
FROM	 [msdb].[dbo].[sysjobs] AS [jobs] WITh(NOLOCK) 
LEFT OUTER JOIN [msdb].[dbo].[sysjobschedules] AS [jobschedule] WITh(NOLOCK)  ON [jobs].[job_id] = [jobschedule].[job_id] 
LEFT OUTER JOIN [msdb].[dbo].[sysschedules] AS [schedule] WITh(NOLOCK)  ON [jobschedule].[schedule_id] = [schedule].[schedule_id] 
INNER JOIN [msdb].[dbo].[syscategories] [categories] WITh(NOLOCK)  ON [jobs].[category_id] = [categories].[category_id] 
LEFT OUTER JOIN (	SELECT	 [job_id],
							 [AvgDuration] = (SUM((([run_duration] / 10000 * 3600) + (([run_duration] % 10000) / 100 * 60) + 
																 ([run_duration] % 10000) % 100)) * 1.0) / COUNT([job_id])
					FROM	 [msdb].[dbo].[sysjobhistory] WITh(NOLOCK)
					WHERE	 [step_id] = 0 
					GROUP BY [job_id]  ) AS [jobhistory]  ON [jobhistory].[job_id] = [jobs].[job_id]

where 1=1
and [jobs].[name] like '%backup%'



 

Detect long Running Jobs Store Proc

We already have a query to detect long running jobs:

http://www.cookingsql.com/2017/02/detect-long-running-jobs-store-proc/

So the next logical step is to encapsulate the code on your DBA database and run it all the time. If a job is considered "long running", we should alert the DBA team.

There are several things we need to take in consideration:

  • Define what a long running job is.
  • Define how much data we are going to use to determine what a long running job is.
  • Ensure that we don't send emails too often.

A long running job is defined as one that's been running longer than two standard deviation above the average.

You can modify this definition within the code, but it's not really recommended.

The question of how much data we are going to use has to do with ensuring the average is meaningful.  Within the code we are taking the average of all data points within the last 30 days, and only consirer jobs that have at least @MinHistExecutions executions (with 10 the default).  Additionally, we are only interested on jobs that their duration takes longer than @MinAvgSecsDuration (with 5 mins the default).  The idea of these parameters is to reduce excessive notifications.

Since we don't want to flood the DBA team with notfications, we have within the code some logic that prevents from sending emails more often than 30 mins.

In order to keep track, we are forced to use a table, which should be created within the DBA team's database.

Here is the script to create the table:


USE [DBA]
GO

/****** Object:  Table [dbo].[Jobs_Running_Long]    Script Date: 1/27/2017 3:16:15 PM ******/

IF OBJECT_id('dbo.Jobs_Running_Long') IS NOT NULL
DROP TABLE [dbo].[Jobs_Running_Long]
GO

/****** Object:  Table [dbo].[Jobs_Running_Long]    Script Date: 1/27/2017 3:16:15 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Jobs_Running_Long](
	[job_id]	 uniqueidentifier	NOT NULL,
	[JobName]	 sysname			NOT NULL,
	[State]		 varchar(29)		NOT NULL,
	[ExecutionDate]	datetime		NOT NULL,
	[AverageSecs] numeric(38, 6)	NOT NULL,
	[DurationSecs] float			NOT NULL,
	[capture_time] datetime			NULL,
	[email_done]   BIT				NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

After we create the table, we can create the Store Procedure, which is the one that does all the work:


USE DBA
GO
IF OBJECT_ID('dbO.SP_DETECT_LONG_RUNNING_JOBS') IS NOT NULL
drop procedure SP_DETECT_LONG_RUNNING_JOBS

go


CREATE PROCEDURE SP_DETECT_LONG_RUNNING_JOBS 
   @MinHistExecutions int   = 10.0
  ,@MinAvgSecsDuration int  = 600.0
  ,@JOB_STATE INT = 1
  ,@skip_email INT = 0
AS
BEGIN
/*=============================================
  File: long_running_jobs.sql
 
  Author: Thomas LaRock, http://thomaslarock.com/contact-me/
 
 MODIFIED AND ADAPTED BY MIGUEL QUINTANA 01/27/2017

  Summary: This script will check to see if any currently
			running jobs are running long. 
Sample:
exec SP_DETECT_LONG_RUNNING_JOBS @MinHistExecutions=5, @MinAvgSecsDuration =600 , @JOB_STATE =4 , @skip_email = 1

 
  Variables:
	@MinHistExecutions - Minimum number of job runs we want to consider 
	@MinAvgSecsDuration - Threshold for minimum duration we care to monitor
        @HistoryStartDate - Start date for historical average
        @HistoryEndDate - End date for historical average
 
        These variables allow for us to control a couple of factors. First
        we can focus on jobs that are running long enough on average for
        us to be concerned with (say, 30 seconds or more). Second, we can
        avoid being alerted by jobs that have run so few times that the
        average and standard deviations are not quite stable yet. This script
        leaves these variables at 1.0, but I would advise you alter them
        upwards after testing.
 
  Returns: One result set containing a list of jobs that
	are currently running and are running longer than two standard deviations 
        away from their historical average. The "Min Threshold" column
        represents the average plus two standard deviations. 
 
  Date: October 3rd, 2012
 
  SQL Server Versions: SQL2005, SQL2008, SQL2008R2, SQL2012
 
  You may alter this code for your own purposes. You may republish
  altered code as long as you give due credit. 
 
  THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
  OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
  LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR
  FITNESS FOR A PARTICULAR PURPOSE.
 
=============================================*/

SET NOCOUNT ON

DECLARE   
	@HistoryStartDate datetime 
   ,@HistoryEndDate datetime  
   
SET @HistoryStartDate = DateAdd(dd,-30,GETDATE()) --'19000101'
SET @HistoryEndDate = GETDATE()

Declare @DBATeam nvarchar(100) = 'DBASpt@cir2.com'



/***** DO NOT MODIFY BELOW *********/
DECLARE @RESULTS TABLE (
	[job_id] [uniqueidentifier] NOT NULL,
	[JobName] [sysname] NOT NULL,
	[State] [varchar](29) NOT NULL,
	[ExecutionDate] [datetime] NULL,
	[AverageSecs] [numeric](38, 6) NULL,
	[DurationSecs] [float] NULL,
	[capture_time] [datetime] NOT NULL
)

 
DECLARE @currently_running_jobs TABLE (
    job_id UNIQUEIDENTIFIER NOT NULL
    ,last_run_date INT NOT NULL
    ,last_run_time INT NOT NULL
    ,next_run_date INT NOT NULL
    ,next_run_time INT NOT NULL
    ,next_run_schedule_id INT NOT NULL
    ,requested_to_run INT NOT NULL
    ,request_source INT NOT NULL
    ,request_source_id SYSNAME NULL
    ,running INT NOT NULL
    ,current_step INT NOT NULL
    ,current_retry_attempt INT NOT NULL
    ,job_state INT NOT NULL
    ) 
 
--capture details on jobs
INSERT INTO @currently_running_jobs
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,''
 
;WITH JobHistData AS
(
  SELECT job_id
	,date_executed=msdb.dbo.agent_datetime(run_date, run_time)
	,secs_duration=run_duration/10000*3600
                      +run_duration%10000/100*60
                      +run_duration%100
  FROM msdb.dbo.sysjobhistory
  WHERE step_id = 0   --Job Outcome
  AND run_status = 1  --Succeeded
)
,JobHistStats AS
(
  SELECT job_id
        ,AvgDuration = AVG(secs_duration*1.)
        ,AvgPlus2StDev = AVG(secs_duration*1.) + 2*stdevp(secs_duration)
  FROM JobHistData
  WHERE date_executed >= DATEADD(day, DATEDIFF(day,'19000101',@HistoryStartDate),'19000101')
  AND date_executed < DATEADD(day, 1 + DATEDIFF(day,'19000101',@HistoryEndDate),'19000101')   
  GROUP BY job_id   HAVING COUNT(*) >= @MinHistExecutions
  AND AVG(secs_duration*1.) >= @MinAvgSecsDuration
)

INSERT INTO  @RESULTS 
SELECT JHS.job_id	
		,j.name AS [JobName]
      ,case crj.job_state
		when 0 then 'not idle or suspended'
		when 1 then	'Executing'
		when 2 then	'Waiting for thread'
		when 3 then	'Between retries'
		when 4 then	'Idle'
		when 5 then	'Suspended'
		when 7 then	'Performing completion actions'
		else ''
 	   end as [State]
	  ,MAX(act.start_execution_date) AS [ExecutionDate]
      ,AvgDuration AS [AverageSecs]
      ,AvgPlus2StDev AS [DurationSecs]
	  ,GETDATE() AS [email_Done]
FROM JobHistData jd
JOIN JobHistStats jhs on jd.job_id = jhs.job_id
JOIN msdb..sysjobs j on jd.job_id = j.job_id
JOIN @currently_running_jobs crj ON crj.job_id = jd.job_id
JOIN msdb..sysjobactivity AS act ON act.job_id = jd.job_id
AND act.stop_execution_date IS NULL
AND act.start_execution_date IS NOT NULL

WHERE 1=1
AND secs_duration > AvgPlus2StDev
AND DATEDIFF(SS, act.start_execution_date, GETDATE()) > AvgPlus2StDev

-- THIS SECTION EXCLUDE JOBS ALREADY IN THE SYSTEM FOR LESS THAN 30 MINS
AND JHS.job_id NOT IN (SELECT A.JOB_ID FROM DBA.dbo.JOBS_Running_Long A WHERE DATEDIFF(mi,A.CAPTURE_TIME,GETDATE()) < 30)


AND crj.job_state = @JOB_STATE


GROUP BY  JHS.job_id, j.name, AvgDuration, AvgPlus2StDev, crj.job_state


IF @SKIP_EMAIL = 1

BEGIN

SELECT JobName, State, ExecutionDate,
convert(decimal(8,1),AverageSecs/60) 'Average in Mins' ,
convert(decimal(8,1),DurationSecs/60) 'Duration in Mins'
FROM @RESULTS

END


IF @skip_email = 0 AND @JOB_STATE = 1
BEGIN


INSERT INTO DBA.dbo.Jobs_Running_Long
SELECT [job_id]
      ,[JobName]
      ,[State]
      ,[ExecutionDate]
      ,[AverageSecs]
      ,[DurationSecs]
	  ,[capture_time]
	  ,NULL 
FROM @RESULTS


-- /*  Builds an Email for the DBAs

DECLARE @SUBJECT NVARCHAR(100)
DECLARE @BODY NVARCHAR(MAX)
DECLARE @xml NVARCHAR(MAX)

SET @SUBJECT = 'LONG RUNNING QUERY DETECTED ON '+CAST(SERVERPROPERTY('SERVERNAME') AS VARCHAR(15))
SET @xml = CAST((
SELECT
td =					  [JobName],'',
[td/@align]='right', td = convert(decimal(10,2),[AverageSecs]/60),'',
[td/@align]='right', td = convert(decimal(10,2),[DurationSecs]/60),''
FROM  DBA.DBO.JOBS_RUNNING_LONG
WHERE email_done is null 

FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @body =
'

Long running Job Detected

One or more jobs were found to be running longer than average, please investigate. Please note, the job might have stopped by the time you receive this message. Check the table dba.dbo.jobs_running_long for details

' SET @body = @body + @xml +'
Job Name AverageDuration (Mins) Duration_Mins
' IF (SELECT COUNT(*) FROM DBA.DBO.JOBS_RUNNING_LONG WHERE email_done is null)>0 begin EXEC MSDB.DBO.SP_SEND_DBMAIL @RECIPIENTS = @DBATeam, @SUBJECT = @SUBJECT, @BODY = @BODY, @body_format ='HTML' end UPDATE DBA.DBO.JOBS_RUNNING_LONG SET email_done = 1 WHERE email_done IS NULL END -- SKIP EMAIL ELSE PRINT '@SKIP EMAIL = 0 MUST BE USED WITH @JOB_STATE = 1. Aborting Email' END

It is important to note that the store proc uses the default mail profile.

After all of this is created, the DBA team can run the following command to check the status of jobs:

-- For running jobs
exec SP_DETECT_LONG RUNNING_JOBS @MinHistExecutions=5, @MinAvgSecsDuration =600 , @JOB_STATE =1 , @skip_email = 1
-- For idle jobs
exec SP_DETECT_LONG_RUNNING_JOBS @MinHistExecutions=5, @MinAvgSecsDuration =600 , @JOB_STATE =4 , @skip_email = 1

After the store proc is ready, We can create a job that runs every 15 mins with one of the statements above.

 

.

Detecting Long Running Jobs

This script is taken from Thomas LaRock, and I'm shamelessly modifying it a little… just a little.

The original article is here

https://thomaslarock.com/2012/10/how-to-find-currently-running-long-sql-agent-jobs/

 

/*=============================================
  
  Author: Thomas LaRock, http://thomaslarock.com/contact-me/
 
 MODIFIED AND ADAPTED BY MIGUEL QUINTANA 01/27/2017

  Summary: This script will check to see if any currently
			running jobs are running long. 
 
  Variables:
	@MinHistExecutions - Minimum number of job runs we want to consider 
	@MinAvgSecsDuration - Threshold for minimum duration we care to monitor
        @HistoryStartDate - Start date for historical average
        @HistoryEndDate - End date for historical average
 
        These variables allow for us to control a couple of factors. First
        we can focus on jobs that are running long enough on average for
        us to be concerned with (say, 30 seconds or more). Second, we can
        avoid being alerted by jobs that have run so few times that the
        average and standard deviations are not quite stable yet. This script
        leaves these variables at 1.0, but I would advise you alter them
        upwards after testing.
 
  Returns: One result set containing a list of jobs that
	are currently running and are running longer than two standard deviations 
        away from their historical average. The "Min Threshold" column
        represents the average plus two standard deviations. 
 
  Date: October 3rd, 2012
 
  SQL Server Versions: SQL2005, SQL2008, SQL2008R2, SQL2012
 
  You may alter this code for your own purposes. You may republish
  altered code as long as you give due credit. 
 
  THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
  OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
  LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR
  FITNESS FOR A PARTICULAR PURPOSE.
 
=============================================*/
SET NOCOUNT ON

DECLARE   
	@HistoryStartDate datetime 
   ,@HistoryEndDate datetime
   ,@JOB_STATE INT = 4
   -- @JOB_STATE = 4 idle, @JOB_STATE = 1 running
   ,@MinHistExecutions int   = 10.0
  ,@MinAvgSecsDuration int  = 600.0  
   
SET @HistoryStartDate = DateAdd(dd,-30,GETDATE()) --'19000101'
SET @HistoryEndDate = GETDATE()


/***** DO NOT MODIFY BELOW *********/
DECLARE @RESULTS TABLE (
	[job_id] [uniqueidentifier] NOT NULL,
	[JobName] [sysname] NOT NULL,
	[State] [varchar](29) NOT NULL,
	[ExecutionDate] [datetime] NULL,
	[AverageSecs] [numeric](38, 6) NULL,
	[DurationSecs] [float] NULL,
	[capture_time] [datetime] NOT NULL
)

 
DECLARE @currently_running_jobs TABLE (
    job_id UNIQUEIDENTIFIER NOT NULL
    ,last_run_date INT NOT NULL
    ,last_run_time INT NOT NULL
    ,next_run_date INT NOT NULL
    ,next_run_time INT NOT NULL
    ,next_run_schedule_id INT NOT NULL
    ,requested_to_run INT NOT NULL
    ,request_source INT NOT NULL
    ,request_source_id SYSNAME NULL
    ,running INT NOT NULL
    ,current_step INT NOT NULL
    ,current_retry_attempt INT NOT NULL
    ,job_state INT NOT NULL
    ) 
 
--capture details on jobs
INSERT INTO @currently_running_jobs
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,''
 
;WITH JobHistData AS
(
  SELECT job_id
	,date_executed=msdb.dbo.agent_datetime(run_date, run_time)
	,secs_duration=run_duration/10000*3600
                      +run_duration%10000/100*60
                      +run_duration%100
  FROM msdb.dbo.sysjobhistory
  WHERE step_id = 0   --Job Outcome
  AND run_status = 1  --Succeeded
)
,JobHistStats AS
(
  SELECT job_id
        ,AvgDuration = AVG(secs_duration*1.)
        ,AvgPlus2StDev = AVG(secs_duration*1.) + 2*stdevp(secs_duration)
  FROM JobHistData
  WHERE date_executed >= DATEADD(day, DATEDIFF(day,'19000101',@HistoryStartDate),'19000101')
  AND date_executed < DATEADD(day, 1 + DATEDIFF(day,'19000101',@HistoryEndDate),'19000101')   
  GROUP BY job_id   HAVING COUNT(*) >= @MinHistExecutions
  AND AVG(secs_duration*1.) >= @MinAvgSecsDuration
)

-- INSERT INTO  @RESULTS 
SELECT JHS.job_id	
		,j.name AS [JobName]
      ,case crj.job_state
		when 0 then 'not idle or suspended'
		when 1 then	'Executing'
		when 2 then	'Waiting for thread'
		when 3 then	'Between retries'
		when 4 then	'Idle'
		when 5 then	'Suspended'
		when 7 then	'Performing completion actions'
		else ''
 	   end as [State]
	  ,MAX(act.start_execution_date) AS [ExecutionDate]
      ,AvgDuration AS [AverageSecs]
      ,AvgPlus2StDev AS [DurationSecs]
	  ,GETDATE() AS [email_Done]
FROM JobHistData jd
JOIN JobHistStats jhs on jd.job_id = jhs.job_id
JOIN msdb..sysjobs j on jd.job_id = j.job_id
JOIN @currently_running_jobs crj ON crj.job_id = jd.job_id
JOIN msdb..sysjobactivity AS act ON act.job_id = jd.job_id
AND act.stop_execution_date IS NULL
AND act.start_execution_date IS NOT NULL

WHERE 1=1
AND secs_duration > AvgPlus2StDev
AND DATEDIFF(SS, act.start_execution_date, GETDATE()) > AvgPlus2StDev

-- THIS SECTION EXCLUDE JOBS ALREADY IN THE SYSTEM FOR LESS THAN 30 MINS
AND JHS.job_id NOT IN (SELECT A.JOB_ID FROM DBA.dbo.JOBS_Running_Long A WHERE DATEDIFF(mi,A.CAPTURE_TIME,GETDATE()) < 30)


AND crj.job_state = @JOB_STATE


GROUP BY  JHS.job_id, j.name, AvgDuration, AvgPlus2StDev, crj.job_state

 

 

Duration of SQL Jobs

List SQL Job duration

If you need step details, see http://www.cookingsql.com/2017/02/duration-of-sql-jobs-steps/

/*
DESCRIPTION	List duration of jobs 
CONFIGURATION none
some filtering might be used

Compatibility List
SQL 2008 and up

*/
select 
 j.name as 'JobName',
 msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
 ((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) 
         as 'RunDurationMinutes'
From msdb.dbo.sysjobs j 
INNER JOIN msdb.dbo.sysjobhistory h  ON j.job_id = h.job_id 
where 1=1
and j.enabled = 1   --Only Enabled Jobs
and h.step_id = 0   --Only show the total duration
and j.name like '%backup%'           --Uncomment to search for a single job
/*
and msdb.dbo.agent_datetime(run_date, run_time) 
BETWEEN '01/01/2017' and '02/01/2017'  --Uncomment for date range queries
*/
order by JobName, RunDateTime desc

 

 

Failed Job History

List all jobs that have failed in the last week

 

/*
DESCRIPTION    List Jobs that have failed in the last 7 days.
CONFIGURATION   none
 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2000
 */

SELECT 
DISTINCT msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
t2.name 'Job Name',
T1.step_id AS 'Step_id', 
T1.step_name AS 'Step Name',
 
case
when run_status=0 then 'Failed'
when run_status=1 then 'Succeeded'
when run_status=2 then 'Retry'
when run_status=3 then 'Canceled'
else ''
end as Status,
LEFT(T1.[message],500) AS 'Error Message'

  
 FROM msdb..sysjobhistory T1 
 JOIN msdb..sysjobs T2 ON T1.job_id = T2.job_id
 
  WHERE 
  T1.run_status NOT IN (1,4) 
  AND T1.step_id != 0 
  and msdb.dbo.agent_datetime(run_date, run_time) > DateAdd(day,-7,GetDate())