Job step wise duration counts

select step_id [Step No],step_name [Step Name],case when LastRunDuration is null then NULL else STUFF(CONVERT(CHAR(8), DATEADD(SECOND, ABS(LastRunDuration), '19000101'), 8), 1, 2, CAST(LastRunDuration / 3600 AS VARCHAR(12))) end LastRunDuration
   FROM (select step_id,step_name, CASE
          WHEN run_duration > 0
          THEN
               (run_duration / 1000000) * (3600 * 24)
             + (run_duration / 10000 % 100) * 3600
             + (run_duration / 100 % 100) * 60
             + (run_duration % 100)
          ELSE
             NULL
       END AS LastRunDuration from msdb.dbo.sysjobhistory where job_id='A09DCA56-E235-4A87-A3D7-C3BB9D823F20' and run_status=1 and step_id <> 0
   and run_date >= (select MAX(run_date) from msdb.dbo.sysjobhistory where step_name='CHECK_job_to_complete')
   ) T

Job Schedule and Execution Details

IF EXISTS (SELECT *
           FROM    tempdb.dbo.sysobjects
           WHERE   id = OBJECT_ID(N'[tempdb].[dbo].[JobHistoryInformation]')
                )
        DROP TABLE [tempdb].[dbo].[JobHistoryInformation]
GO
CREATE TABLE [tempdb].[dbo].[JobHistoryInformation]
(
        job_id uniqueidentifier NOT NULL,
         last_run_date nvarchar (20) NOT NULL,
         last_run_time nvarchar (20) NOT NULL,
         next_run_date nvarchar (20) NOT NULL,
         next_run_time nvarchar (20) NOT NULL,
         next_run_schedule_id INT NOT NULL,
         requested_to_run INT NOT NULL,
         request_source INT NOT NULL,
         request_source_id sysname
                   COLLATE database_default NULL,
         running INT NOT NULL,
         current_step INT NOT NULL,
         current_retry_attempt INT NOT NULL,
         job_state INT NOT NULL
 )
   
DECLARE @job_owner   sysname
DECLARE @is_sysadmin   INT
SET @is_sysadmin   = isnull (is_srvrolemember ('sysadmin'), 0)
SET @job_owner   = suser_sname ()
       
INSERT INTO [tempdb].[dbo].[JobHistoryInformation]
   EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner

SELECT job_name,StartTime ScheduledStartTime,EndTime ScheduledEndTime,next_scheduled_run_date,
LastRunOutcome,case when LastRunDuration is null then NULL else STUFF(CONVERT(CHAR(8), DATEADD(SECOND, ABS(LastRunDuration), '19000101'), 8), 1, 2, CAST(LastRunDuration / 3600 AS VARCHAR(12))) end LastRunDuration,
schedule_name,[Description],Freq_Type,Freq_Interval
FROM
(
SELECT SJ.name as job_name
, SJ.enabled as is_job_enabled
, SS.enabled as is_schedule_enabled
/*, ISNULL(SJ.description, '') as job_desc*/
, SS.name as schedule_name
, CASE freq_type
WHEN 1 THEN 'Occurs on ' + STUFF(RIGHT(active_start_date, 4), 3,0, '/') + '/' + LEFT(active_start_date, 4) + ' at '
+ REPLACE( RIGHT(CONVERT(varchar(30),
CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime) /* hh:mm:ss 24H */
, 9), 14), ':000', ' ') /* HH:mm:ss:000AM/PM then replace the :000 with space.*/
WHEN 4 THEN 'Occurs every ' + CAST(freq_interval as varchar(10))
+ ' day(s) '
+ CASE freq_subday_type
WHEN 1 THEN 'at '+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' '))
WHEN 2 THEN 'every ' + CAST(freq_subday_interval as varchar(10)) + ' second(s)'
WHEN 4 THEN 'every ' + CAST(freq_subday_interval as varchar(10)) + ' minute(s)'
WHEN 8 THEN 'every ' + CAST(freq_subday_interval as varchar(10)) + ' hour(s)'
ELSE '' END
+ CASE WHEN freq_subday_type in (2,4,8) /* repeat seconds/mins/hours */
THEN ' between '+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' '))
+ ' and '
+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_end_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' '))
ELSE ''
END
WHEN 8 THEN
'Occurs every ' + CAST(freq_recurrence_factor as varchar(10))
+ ' week(s) on '
+
REPLACE( CASE WHEN freq_interval&1 = 1 THEN 'Sunday, ' ELSE '' END
+ CASE WHEN freq_interval&2 = 2 THEN 'Monday, ' ELSE '' END
+ CASE WHEN freq_interval&4 = 4 THEN 'Tuesday, ' ELSE '' END
+ CASE WHEN freq_interval&8 = 8 THEN 'Wednesday, ' ELSE '' END
+ CASE WHEN freq_interval&16 = 16 THEN 'Thursday, ' ELSE '' END
+ CASE WHEN freq_interval&32 = 32 THEN 'Friday, ' ELSE '' END
+ CASE WHEN freq_interval&64 = 64 THEN 'Saturday, ' ELSE '' END
+ '|', ', |', ' ') /* get rid of trailing comma */
+ CASE freq_subday_type
WHEN 1 THEN 'at '+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' '))
WHEN 2 THEN 'every ' + CAST(freq_subday_interval as varchar(10)) + ' second(s)'
WHEN 4 THEN 'every ' + CAST(freq_subday_interval as varchar(10)) + ' minute(s)'
WHEN 8 THEN 'every ' + CAST(freq_subday_interval as varchar(10)) + ' hour(s)'
ELSE '' END
+ CASE WHEN freq_subday_type in (2,4,8) /* repeat seconds/mins/hours */
THEN ' between '+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' '))
+ ' and '
+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_end_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' '))
ELSE ''
END
WHEN 16 THEN
'Occurs every ' + CAST(freq_recurrence_factor as varchar(10))
+ ' month(s) on '
+ 'day ' + CAST(freq_interval as varchar(10)) + ' of that month '
+ CASE freq_subday_type
WHEN 1 THEN 'at '+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' '))
WHEN 2 THEN 'every ' + CAST(freq_subday_interval as varchar(10)) + ' second(s)'
WHEN 4 THEN 'every ' + CAST(freq_subday_interval as varchar(10)) + ' minute(s)'
WHEN 8 THEN 'every ' + CAST(freq_subday_interval as varchar(10)) + ' hour(s)'
ELSE '' END
+ CASE WHEN freq_subday_type in (2,4,8) /* repeat seconds/mins/hours */
THEN ' between '+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' '))
+ ' and '
+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_end_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' '))
ELSE ''
END
WHEN 32 THEN
'Occurs ' +
CASE freq_relative_interval
WHEN 1 THEN 'every first '
WHEN 2 THEN 'every second '
WHEN 4 THEN 'every third '
WHEN 8 THEN 'every fourth '
WHEN 16 THEN 'on the last '
END
+ CASE 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'
END
+ ' of every ' + CAST(freq_recurrence_factor as varchar(10)) + ' month(s) '
+ CASE freq_subday_type
WHEN 1 THEN 'at '+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' '))
WHEN 2 THEN 'every ' + CAST(freq_subday_interval as varchar(10)) + ' second(s)'
WHEN 4 THEN 'every ' + CAST(freq_subday_interval as varchar(10)) + ' minute(s)'
WHEN 8 THEN 'every ' + CAST(freq_subday_interval as varchar(10)) + ' hour(s)'
ELSE '' END
+ CASE WHEN freq_subday_type in (2,4,8) /* repeat seconds/mins/hours */
THEN ' between '+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' '))
+ ' and '
+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_end_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' '))
ELSE ''
END
WHEN 64 THEN 'Runs when the SQL Server Agent service starts'
WHEN 128 THEN 'Runs when the computer is idle'
END as [Description]
, CASE freq_type
WHEN 1 THEN 'One Time'
WHEN 4 THEN CASE freq_subday_type WHEN 1 THEN 'Daily'
WHEN 2 THEN 'Second-ly'
WHEN 4 THEN 'Minutely'
WHEN 8 THEN 'Hourly'
ELSE ''
END
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly, relative to freq_interval'
WHEN 64 THEN 'Runs when the SQL Server Agent service starts'
WHEN 128 THEN 'Runs when the computer is idle'
END As [Freq_Type]
, CASE freq_subday_type
WHEN 1 THEN 'At the specified time'
WHEN 2 THEN 'Every ' + CAST(freq_subday_interval as varchar(10)) + ' Second(s)'
WHEN 4 THEN 'Every ' + CAST(freq_subday_interval as varchar(10)) + ' Minute(s)'
WHEN 8 THEN 'Every ' + CAST(freq_subday_interval as varchar(10)) + ' Hour(s)'
ELSE ''
END AS freq_subday_type
, CASE
WHEN freq_type = 4 /* (daily) */ THEN 'Every ' + CAST(freq_interval as varchar(10)) + ' Day(s)'
WHEN freq_type = 8 /* (weekly) */ THEN
REPLACE( CASE WHEN freq_interval&1 = 1 THEN 'Sunday, ' ELSE '' END
+ CASE WHEN freq_interval&2 = 2 THEN 'Monday, ' ELSE '' END
+ CASE WHEN freq_interval&4 = 4 THEN 'Tuesday, ' ELSE '' END
+ CASE WHEN freq_interval&8 = 8 THEN 'Wednesday, ' ELSE '' END
+ CASE WHEN freq_interval&16 = 16 THEN 'Thursday, ' ELSE '' END
+ CASE WHEN freq_interval&32 = 32 THEN 'Friday, ' ELSE '' END
+ CASE WHEN freq_interval&64 = 64 THEN 'Saturday, ' ELSE '' END
+ '|', ', |', ' ') /* get rid of trailing comma */
WHEN freq_type = 16 THEN 'On Day ' + CAST(freq_interval as varchar(10)) + ' of Every Month'
WHEN freq_type = 32 /* (monthly) */ THEN 'Every ' +
CASE 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
ELSE ''
END as [freq_interval]
, CASE WHEN freq_type = 8 /* (weekly) */ THEN 'Every ' + CAST(freq_recurrence_factor as varchar(10)) + ' Week(s)'
WHEN freq_type = 16 /* (monthly) */ THEN 'Every ' + CAST(freq_recurrence_factor as varchar(10)) + ' Month(s)'
WHEN freq_type = 32 /* (monthly relative) */ THEN 'Every ' + CAST(freq_recurrence_factor as varchar(10)) + ' Week(s)'
ELSE ''
END as freq_recurrence_factor
, LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' ')) as starttime
, LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_end_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' ')) as endtime
, STUFF(STUFF(active_start_date, 5, 0, '/'), 8, 0, '/') as schedule_start_date
, STUFF(STUFF(active_end_date, 5, 0, '/'), 8, 0, '/') as schedule_end_date
 ,ja.next_scheduled_run_date
 ,CASE h.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Completed' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Cancel' WHEN 4 THEN 'Running' END AS LastRunOutcome
       ,CASE
          WHEN h.run_duration > 0
          THEN
               (h.run_duration / 1000000) * (3600 * 24)
             + (h.run_duration / 10000 % 100) * 3600
             + (h.run_duration / 100 % 100) * 60
             + (h.run_duration % 100)
          ELSE
             NULL
       END AS LastRunDuration
FROM msdb.dbo.sysjobs SJ
LEFT Join [tempdb].[dbo].[JobHistoryInformation] x
ON x.job_id = SJ.job_id
LEFT JOIN msdb.dbo.sysjobschedules SJS
ON SJ.job_id = SJS.job_id
LEFT JOIN msdb.dbo.sysschedules SS
ON SJS.schedule_id = SS.schedule_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory h ON x.job_id = h.job_id AND x.last_run_date = h.run_date
          AND x.last_run_time = h.run_time AND h.step_id = 0
LEFT OUTER JOIN msdb.dbo.sysjobactivity ja on ja.job_history_id=h.instance_id and ja.job_id=SJ.job_id
) TEMPTA 

SQL Agent Job Error CMDEXEC subsystem failed to load

We have been recently migrating a lot of physical clusters to virtual. The quickest way to move sql agent jobs was to restore MSDB database. To validate if jobs were in order we executed our System Backup job and surprisingly it went in ‘Suspended’ mode. Investigation for the failed job revealed:

[LOG] Step 1 of job ‘DatabaseBackup – SYSTEM_DATABASES – FULL’ (0xCE446B0C82AA3141B2898110806513A1) cannot be run because the CMDEXEC subsystem failed to load. The job has been suspended

Upon further investigation of the agent log:

1 2 3 4 5 6 7 8 9 10 11 12 13

exec xp_readerrorlog 0,2 2015-04-09 14:50:22.000 1 [125] Subsystem 'ActiveScripting' could not be loaded (reason: The specified module could not be found) 2015-04-09 14:50:22.000 1 [125] Subsystem 'CmdExec' could not be loaded (reason: The specified module could not be found) 2015-04-09 14:50:22.000 1 [125] Subsystem 'Snapshot' could not be loaded (reason: The specified module could not be found) 2015-04-09 14:50:22.000 1 [125] Subsystem 'LogReader' could not be loaded (reason: The specified module could not be found) 2015-04-09 14:50:22.000 1 [125] Subsystem 'Distribution' could not be loaded (reason: The specified module could not be found) 2015-04-09 14:50:22.000 1 [125] Subsystem 'Merge' could not be loaded (reason: The specified module could not be found) 2015-04-09 14:50:22.000 1 [125] Subsystem 'QueueReader' could not be loaded (reason: The specified module could not be found) 2015-04-09 14:50:22.000 1 [125] Subsystem 'ANALYSISQUERY' could not be loaded (reason: The specified module could not be found) 2015-04-09 14:50:22.000 1 [125] Subsystem 'ANALYSISCOMMAND' could not be loaded (reason: The specified module could not be found) 2015-04-09 14:50:22.000 1 [125] Subsystem 'SSIS' could not be loaded (reason: The specified module could not be found) 2015-04-09 14:50:22.000 1 [125] Subsystem 'PowerShell' could not be loaded (reason: The specified module could not be found)

Don’t panic if you see your sql agent job error stating xxxxx ‘subsytem failed to load’. It certainly does not mean your SQL world is crashing. All it means is the location of the binary files was changed. This was first discovered in SQL2005 and well documented in KB903205. In total there are 11 subsystems as per my testing for SQL 2008/R2 & 2012. Here is the list:

Test Scenario

Migrating MSDB from SQL instances of 2008R2, 2012  –> SQL 2008R2, 2012

Fix

First thing we need to check is the syssubsystem table and determine the location of the dll’s.

1 2 3 4 5 6 7 8

/*This will show the description for each subsystem*/ select ss.subsystem as Subsystem,sm.text as Description,ss.subsystem_dll, ss.agent_exe from msdb.dbo.syssubsystems ss inner join sys.messages sm on ss.description_id = sm.message_id where sm.language_id = 1033 /*This will skip description*/ select * from msdb.dbo.syssubsystems

You will need to pay attention to subsystem_dll column. Check if the files exist in the location. In our case, the old cluster pointed to S:\Program Files\Microsoft SQL Server\MSSQL10_50.xxx\MSSQL\binn\SQLCMDSS.DLL  and on the new cluster it was on C:\Program Files\Microsoft SQL Server\MSSQL10_50.xxx\MSSQL\binn\SQLCMDSS.DLL

In order to do this seamlessly run the following command followed by restart of sql agent

1 2 3 4 5 6 7 8 9 10 11 12

sp_configure "allow updates",1 reconfigure with override go update msdb.dbo.syssubsystems set subsystem_dll = replace(subsystem_dll,'S:\Program Files','C:\Program Files')   from msdb.dbo.syssubsystems where subsystem_dll like 'S:\Program Files%' go sp_configure "allow updates", 0 reconfigure with override

If you do not like messing with MSDB and want your SQL Jobs to be moved between instances, there is an excellent article by Ahmad Osama on  move jobs using powershell