Category Archives: Scripts

This section will store simple scripts

Location of very last Backups — All Databases

One script with some CTE, but two ways to show the info.

A narrow view:

 /*
DESCRIPTION    List location of  all backup for a single database.
				Includes if a Tlog or Dif exists or not
CONFIGURATION    

                          Set @LastXday to limit the number of days to search for

 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2012

 */ 
declare @LastXdays nvarchar(2)
set @LastXdays = '30'

 
;with fullbk as (  
SELECT
--	top 10
	 s.database_name
	, db_id(s.database_name) 'dbid'
	,s.recovery_model
    ,m.physical_device_name
    ,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
    ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken'
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,'full' 'bk_type'
	,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number'
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE 1=1
--    s.database_name like @dbname
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'D'
--ORDER BY  s.backup_finish_date desc
)
, Diffbk as (  
SELECT
--	top 10
	 s.database_name
	, db_id(s.database_name) 'dbid'
	,s.recovery_model
    ,m.physical_device_name
    ,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
    ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken'
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,'Diff' 'bk_type'
	,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number'
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE 1=1
--    s.database_name like @dbname
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'I'
--ORDER BY  s.backup_finish_date desc
)
, TlogBK as (
SELECT
--top 10
	 s.database_name
	, db_id(s.database_name) 'dbid'
	,s.recovery_model
    ,m.physical_device_name
	,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
    ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken'
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,'Tlog' 'bk_type'
	,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number'
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE 1=1
--    s.database_name like @dbname
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'L'
--ORDER BY  s.backup_finish_date desc
)
, final as
(
select a.database_name, a.recovery_model, a.bk_type, a.physical_device_name,a.Seconds_TimeTaken,a.age_in_days, a.backup_start_date
from fullbk a
where 1=1
and a.number  = 1
UNION ALL
select c.database_name, c.recovery_model, 'Diff' 'bk_type', 
coalesce(b.physical_device_name,'') as 'physical_device_name',
b.seconds_TimeTaken,
b.age_in_days,
b.backup_start_date

from Diffbk b right join fullbk c on b.dbid = c.dbid
where 1=1
and c.number  = 1 and coalesce(b.number,1) = 1

UNION ALL

select c.database_name, c.recovery_model, 'tlog' 'bk_type', 
case c.recovery_model
when 'simple' then ''
else b.physical_device_name end as 'physical_device_name',
b.seconds_TimeTaken,
b.age_in_days,
b.backup_start_date

from Tlogbk b right join fullbk c on b.dbid = c.dbid
where 1=1
and c.number  = 1 and coalesce(b.number,1) = 1
)
select * from final
order by 1,3

 

 

A wide view


 /*
DESCRIPTION    List location of  all backup for a single database.
				Includes if a Tlog or Dif exists or not
CONFIGURATION    

                          Set @LastXday to limit the number of days to search for

Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2012

 */ 
declare @LastXdays nvarchar(2)
set @LastXdays = '30'

 
;with fullbk as (  
SELECT
	 s.database_name
	, db_id(s.database_name) 'dbid'
	,s.recovery_model
    ,m.physical_device_name
    ,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
    ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken'
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,'full' 'bk_type'
	,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number'
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE 1=1
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'D'
)
, Diffbk as (  
SELECT
	 s.database_name
	, db_id(s.database_name) 'dbid'
	,s.recovery_model
    ,m.physical_device_name
    ,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
    ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken'
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,'Diff' 'bk_type'
	,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number'
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE 1=1
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'I'
)
, TlogBK as (
SELECT
	 s.database_name
	, db_id(s.database_name) 'dbid'
	,s.recovery_model
    ,m.physical_device_name
	,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
    ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken'
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,'Tlog' 'bk_type'
	,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number'
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE 1=1
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'L'
)

select a.database_name, a.recovery_model
	, a.bk_type, a.physical_device_name,a.Seconds_TimeTaken,a.age_in_days, a.backup_start_date
	, 'DIFF' 'DIFF_BK', b.physical_device_name,b.Seconds_TimeTaken,b.age_in_days, b.backup_start_date
	,  'TLOG' 'TLOG_BK', c.physical_device_name,c.Seconds_TimeTaken,c.age_in_days, c.backup_start_date
from fullbk a
left join Diffbk b on a.dbid = b.dbid
left join TlogBK c on a.dbid = c.dbid

where 1=1
and a.number  = 1 
and coalesce(b.number,1)=1
and coalesce(c.number,1)=1

union all
select Distinct
    convert(varchar(30),name) as DBname
    ,DATABASEPROPERTYEX(name,'recovery') 'RecoveryModel'
    ,'NO BACKUPS RECORDED ON MSDB',NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL
 
from master..sysdatabases as record
where
    name not in (select distinct database_name from msdb..backupset)
    and name not in ('tempdb','pubs','northwind','model')
order by 1,2


Location of very last Backups

This script will list the location of the last Full, Differential and Tlog (if exists)

I use three CTE and at the end I use a UNION all

the last two select statements are joins so it displays NULL if either Diff or Tlog do not exist

 

 /*
DESCRIPTION    List location of  all backup for a single database.
				Includes if a Tlog or Dif exists or not
CONFIGURATION    

                          Set @LastXday to limit the number of days to search for

                          Set @dbname for the database

 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2012
MSSQL2014

 */ 
declare @dbname varchar(500)
declare @LastXdays nvarchar(2)
set @dbname = db_name()
--SET @dbname = 'ELMO'
set @LastXdays = '30'
 
;with fullbk as (  
SELECT
	top 1
	 s.database_name
	, db_id(s.database_name) 'dbid'
	,s.recovery_model
    ,m.physical_device_name
    ,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
    ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken'
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,'full' 'bk_type'
	,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number'
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE
    s.database_name like @dbname
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'D'
ORDER BY  s.backup_finish_date desc
)
, Diffbk as (  
SELECT
	top 1
	 s.database_name
	, db_id(s.database_name) 'dbid'
	,s.recovery_model
    ,m.physical_device_name
    ,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
    ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken'
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,'Diff' 'bk_type'
	,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number'
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE
    s.database_name like @dbname
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'I'
ORDER BY  s.backup_finish_date desc
)
, TlogBK as (
SELECT
top 1
	 s.database_name
	, db_id(s.database_name) 'dbid'
	,s.recovery_model
    ,m.physical_device_name
	,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
    ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken'
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,'Tlog' 'bk_type'
	,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number'
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE
    s.database_name like @dbname
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'L'
ORDER BY  s.backup_finish_date desc
)

select a.database_name, a.recovery_model, a.bk_type, a.physical_device_name,a.bk_type,a.Seconds_TimeTaken,a.age_in_days, a.backup_start_date
from fullbk a
where 1=1
and a.number  = 1
UNION ALL
select c.database_name, c.recovery_model, 'Diff', 
coalesce(b.physical_device_name,'') as 'physical_device_name',
b.bk_type,
b.seconds_TimeTaken,
b.age_in_days,
b.backup_start_date

from Diffbk b right join fullbk c on b.dbid = c.dbid
where 1=1
and c.number  = 1		 

UNION ALL
select c.database_name, c.recovery_model, 'tlog', 
case c.recovery_model
when 'simple' then ''
else b.physical_device_name end as 'physical_device_name',
b.bk_type,
b.seconds_TimeTaken,
b.age_in_days,
b.backup_start_date

from Tlogbk b right join fullbk c on b.dbid = c.dbid
where 1=1
and c.number  = 1		 






 

 

 

Drop and Recreate Synonyms

This script is useful to script out all synonyms on a database before restoring on top another copy from another environment.

It is also useful to script out what's there, examine it, and see if any modifications are needed. See this one to verify synonyms

Rememer to change the output to "results to text".

 

SET NOCOUNT ON
SELECT
'IF EXISTS (SELECT NAME FROM SYS.OBJECTS WHERE NAME = '''+NAME+''')
DROP SYNONYM ['+NAME+'];
CREATE SYNONYM ['+NAME+'] FOR '+BASE_OBJECT_NAME+'

'
FROM sys.synonyms


Verify Synonyms

The following script gets a list of the synonyms, parses them, and then the checks if the existence of the object on the sys.objects table.

Any errors encountered are assumed as "not a valid synonym"

/*
Author: Miguel Quintana

Description:
Reads all SYNONYMS on the database and test if they are valid.
It checks their EXISTENCE on the SYSOBJECTS table
and uses the Link Server if available

No configuration needed. Just run it under the DB that you are testing Synonyms

*/


-- this variables are for the cursor
DECLARE @NAME NVARCHAR(100)
  ,@SERVERNAME NVARCHAR(20)
  ,@DBName NVARCHAR(100)
  ,@SCHEMA NVARCHAR(100)
  ,@OBJECTNAME NVARCHAR(1000)
  ,@BaseObjectName NVARCHAR(1000)
-- This are to process the data
DECLARE @CMD NVARCHAR(MAX)
DECLARE @RESULTS TABLE (
  VALUE NVARCHAR(100)
  ,SYNONYM NVARCHAR(100)
  ,BaseObjectName NVARCHAR(100)
  ,Link_SERVER NVARCHAR(100)
  )
DECLARE CUR CURSOR
FOR
SELECT NAME
  ,PARSENAME(base_object_name, 4) ServerName
  ,COALESCE(PARSENAME(base_object_name, 3), DB_NAME(DB_ID())) AS dbName
  ,COALESCE(PARSENAME(base_object_name, 2), SCHEMA_NAME(SCHEMA_ID())) AS schemaName
  ,PARSENAME(base_object_name, 1) AS objectName
  ,BASE_OBJECT_NAME
FROM sys.synonyms
order by ServerName DESC, NAME



OPEN CUR
FETCH NEXT FROM CUR INTO 
   @name
  ,@SERVERNAME
  ,@DBNAME
  ,@Schema
  ,@ObjectName
  ,@BaseObjectName
WHILE @@FETCH_STATUS = 0
BEGIN
  -- Test SYNONYMS that use Link Servers
  IF @SERVERNAME IS NOT NULL
  BEGIN
    SET @cmd = 'EXEC (''IF EXISTS(select name from ' + @DBName + '.[sys].[objects] where name = ''''' + @OBJECTNAME + ''''')
    SELECT ''''VALID SYNONYM'''',''''' + @NAME + ''''',''''' + @BaseObjectName + ''''',''''' + @SERVERNAME + '''''
    ELSE
    SELECT ''''NOT A VALID SYNONYM'''',''''' + @NAME + ''''',''''' + @BaseObjectName + ''''',''''' + @SERVERNAME + ''''''') AT [' + @SERVERNAME + ']'
    print @cmd
	BEGIN TRY
      INSERT INTO @RESULTS
	  EXEC (@cmd)
    END TRY
    -- IF the exec fails for any reason, the SYNONYM is not valid
    BEGIN CATCH
      INSERT INTO @RESULTS
      VALUES (
        'NOT A VALID SYNONYM'
        ,@NAME
        ,@BaseObjectName
        ,@SERVERNAME
        )
    END CATCH
  END
  -- Test SYNONYMS that do not use Link Servers
  ELSE
  BEGIN
    SET @cmd = 'EXEC (''IF EXISTS(select name from ' + @DBName + '.[sys].[objects] where name = ''''' + @OBJECTNAME + ''''')
    SELECT ''''VALID SYNONYM'''',''''' + @NAME + ''''',''''' + @BaseObjectName + ''''','''' ''''
    ELSE
    SELECT ''''NOT A VALID SYNONYM'''',''''' + @NAME + ''''',''''' + @BaseObjectName + ''''',''''' + @@SERVERNAME + ''''''')'
    print @cmd    
	
	BEGIN TRY
      INSERT INTO @RESULTS
      EXEC (@cmd)
    END TRY
    -- IF the exec fails for any reason, the SYSNONYM is not valid
    BEGIN CATCH
      INSERT INTO @RESULTS
      VALUES (
        'NOT A VALID SYNONYM'
        ,@NAME
        ,@BaseObjectName
        ,@@SERVERNAME
        )
    END CATCH
  END
  FETCH NEXT FROM CUR INTO
     @name
    ,@SERVERNAME
    ,@DBNAME
    ,@Schema
    ,@ObjectName
    ,@BaseObjectName
END
CLOSE CUR
DEALLOCATE CUR

SELECT Value
  ,[SYNONYM]
  ,UPPER(Link_SERVER) 'Link Server'
  ,BaseObjectName
  ,COALESCE(ss.data_source,'') 'Data Source'
FROM @RESULTS r
LEFT JOIN master.sys.servers ss on r.Link_SERVER = ss.name


ORDER BY VALUE


Link Servers are supported.

 

Sometimes, it will report as not valid SYNONYM even though you are able to use them (select top 1 * from <sn_syonym_name>). but users without sysadmin cannot.

There are many reasons why that would happen, usually drop & recreate fixes the issue or even running the following:

 


EXEC sp_serveroption @server = '',@optname = 'remote proc transaction promotion', @optvalue = 'false' ;

 

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%'



 

Querying CommandLog

If you have implemented Ola Hallenger scripts, http://ola.hallengren.com/, you will have a CommandLog table where you keep track of execution times.

The following query will help you get information from it, very useful for tracking backup failures:

USE DBA
SELECT
DatabaseName,
StartTime,
DATEDIFF(MINUTE,StartTime,EndTime) 'Duration in Min',
EndTime,
ErrorNumber,
ErrorMessage,
Command,
replace(
cast(SERVERPROPERTY('ErrorLogFileName') as nvarchar(1000))
,'errorlog','')
FROM DBO.COMMANDLOG
WHERE 1=1
--and StartTime > dateadd(dd,-1,getdate())
and CommandType in ('BACKUP_DATABASE','BACKUP_LOG','RESTORE_VERIFYONLY')
--and CommandType in ('xp_create_subdir','xp_delete_file')
--and CommandType in ('DBCC_CHECKDB')

--and ErrorNumber <> 0

order by ID desc

Below, we take advantage of window functions and we can see the last time a database had a DBCC checkdb done, along with its duration

 

    USE DBA
    
	;with Last_DBBC as
	(
	SELECT
    DatabaseName,
    StartTime,
    EndTime,
	ROW_NUMBER() over (PARTITION by databasename order by StartTime desc) 'rownumber'
    FROM DBO.COMMANDLOG
    WHERE 1=1
	and CommandType in ('DBCC_CHECKDB')
   	)
	select
	DatabaseName,
	StartTime, EndTime,
	DATEDIFF(MI,StartTime,EndTime) 'Duration in Min',
	DATEDIFF(DD,StartTime,GETDATE()) 'Age of DBCC in days'
	from Last_DBBC
	where 1=1
	and rownumber = 1

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