1

How can I check what jobs are scheduled to be run at a specific time?

I am trying to work on something,

use msdb
go

Select [job name] = jb.name 
    , [schedule name] = sc.name
    , [next run date] = js.next_run_date
    , [next run time] = js.next_run_time
From dbo.sysjobs as jb
Inner Join dbo.sysjobschedules as js on js.job_id = jb.job_id
Inner Join dbo.sysschedules as sc on js.schedule_id = sc.schedule_id

but I could not finish so far..

Ben has just come back from Pittston (US) and reported slowdowns on Bocss at 11.30 pm (PST) and 4.30 am (GMT) every evening. Can you please check to see if the backups or another process is kicked off around this time.

Thanks

The Conclusion:

Using the script from Bob Klimes below, I get the following list of jobs that I will discuss with the other DBAs here, tomorrow.

enter image description here

this script by Kin is also very useful when dealing with jobs.

I am copying the script here for my own reference, if Kin or anyone is not happy with this, just write a comment below and I will remove it, no problem.

SET NOCOUNT ON
GO

/***********************************************************************************************************
************************************************************************************************************
Author          :       KIN SHAH - Sr.DBA (MS SQL Server and SybaseASE)

Date            :       Feb 18th 2014

Purpose         :       Find the Last_Run_Duration with Avg, Max and Min for SQL Agent RUN Times.
                        This script can be used to do a trend analysis using SSRS.

RDBMS           :       Compatible with SQL Server 2005, 2008 R2, 2012, 2014 and 2016 :-)
                        Compatible with servers running standard and binary Collations as well.

Version         :       1.0.0

Change History  :       ** NONE **

License         :       This script is free to download and use for personal, educational, and internal 
                        corporate purposes, provided that this header is preserved. Redistribution or sale 
                        of this script in whole or in part, is prohibited without the author's express 
                        written consent.

************************************************************************************************************
************************************************************************************************************/

-- DROP all temp tables if existed 
IF OBJECT_ID('tempdb..#temp_runhistory') IS NOT NULL
    DROP TABLE #temp_runhistory;

IF OBJECT_ID('tempdb..#temp_jobhistory') IS NOT NULL
    DROP TABLE #temp_jobhistory;

IF OBJECT_ID('tempdb..#schedules') IS NOT NULL
    DROP TABLE #schedules;

IF OBJECT_ID('tempdb..#finalReport') IS NOT NULL
    DROP TABLE #finalReport;

-- Variable Declaration
DECLARE @num_days INT
DECLARE @first_day DATETIME
DECLARE @last_day DATETIME
DECLARE @first_num INT

IF @num_days IS NULL
    SET @num_days = 30 -- this will be how much the job history will be utilize to analyze the average run times !!
SET @last_day = getdate()
SET @first_day = dateadd(dd, - @num_days, @last_day)

SELECT @first_num = cast(year(@first_day) AS CHAR(4)) + replicate('0', 2 - len(month(@first_day))) + cast(month(@first_day) AS VARCHAR(2)) + replicate('0', 2 - len(day(@first_day))) + cast(day(@first_day) AS VARCHAR(2))

SELECT h.instance_id
    ,h.job_id
    ,j.name
    ,h.step_id
    ,h.step_name
    ,h.sql_message_id
    ,h.sql_severity
    ,h.run_status
    ,'run_date' = cast(h.run_date AS VARCHAR(8))
    ,'run_time' = replicate('0', 6 - len(h.run_time)) + cast(h.run_time AS VARCHAR(6))
    ,'run_datetime' = left(cast(h.run_date AS VARCHAR(8)), 4) + '/' + substring(cast(h.run_date AS VARCHAR(8)), 5, 2) + '/' + right(cast(h.run_date AS VARCHAR(8)), 2) + ' ' + left(replicate('0', 6 - len(h.run_time)) + cast(h.run_time AS VARCHAR(6)), 2) + ':' + substring(replicate('0', 6 - len(h.run_time)) + cast(h.run_time AS VARCHAR(6)), 3, 2) + ':' + right(replicate('0', 6 - len(h.run_time)) + cast(h.run_time AS VARCHAR(6)), 2)
    ,run_duration = cast(h.run_duration AS VARCHAR(20))
    ,run_duration_conv = CASE 
        WHEN (len(cast(h.run_duration AS VARCHAR(20))) < 3)
            THEN cast(h.run_duration AS VARCHAR(6))
        WHEN (len(cast(h.run_duration AS VARCHAR(20))) = 3)
            THEN LEFT(cast(h.run_duration AS VARCHAR(6)), 1) * 60 --min
                + RIGHT(cast(h.run_duration AS VARCHAR(6)), 2) --sec
        WHEN (len(cast(h.run_duration AS VARCHAR(20))) = 4)
            THEN LEFT(cast(h.run_duration AS VARCHAR(6)), 2) * 60 --min
                + RIGHT(cast(h.run_duration AS VARCHAR(6)), 2) --sec
        WHEN (len(cast(h.run_duration AS VARCHAR(20))) >= 5)
            THEN (Left(cast(h.run_duration AS VARCHAR(20)), len(h.run_duration) - 4)) * 3600 --hour
                + (substring(cast(h.run_duration AS VARCHAR(20)), len(h.run_duration) - 3, 2)) * 60 --min
                + Right(cast(h.run_duration AS VARCHAR(20)), 2) --sec
        END
    ,h.retries_attempted
    ,h.server
INTO #temp_jobhistory
FROM msdb..sysjobhistory h
    ,msdb..sysjobs j
WHERE h.job_id = j.job_id
    AND h.run_date >= @first_num
    AND h.step_id = 0
    option (recompile)

SELECT j.job_id
    ,j.name
    ,'Sampling' = (
        SELECT count(*)
        FROM #temp_jobhistory h
        WHERE h.job_id = j.job_id
        )
    ,'fromRunDate' = (
        SELECT min(run_date)
        FROM #temp_jobhistory h
        WHERE h.job_id = j.job_id
        )
    ,'run_duration_max' = (
        SELECT max(run_duration_conv)
        FROM #temp_jobhistory h
        WHERE h.job_id = j.job_id
        )
    ,'run_duration_min' = (
        SELECT min(run_duration_conv)
        FROM #temp_jobhistory h
        WHERE h.job_id = j.job_id
        )
    ,'run_duration_avg' = (
        SELECT avg(run_duration_conv)
        FROM #temp_jobhistory h
        WHERE h.job_id = j.job_id
        )
    ,'Last_RunDate' = (
        SELECT max(run_datetime)
        FROM #temp_jobhistory h
        WHERE h.job_id = j.job_id
        )
    ,'Last_RunStatus' = NULL --(select run_status from #temp_jobhistory h where h.job_id=j.job_id)
    ,'Last_RunDuration' = NULL
INTO #temp_runhistory
FROM msdb..sysjobs j
WHERE j.enabled = 1 -- only look for enabled JOBS !!
option (recompile)

UPDATE #temp_runhistory
SET Last_RunStatus = j.run_status
    ,Last_RunDuration = j.run_duration_conv
FROM #temp_jobhistory j
WHERE #temp_runhistory.job_id = j.job_id
    AND #temp_runhistory.Last_RunDate = j.run_datetime
    AND j.run_datetime = (
        SELECT max(run_datetime)
        FROM #temp_jobhistory j1
        WHERE j1.job_id = #temp_runhistory.job_id
        )

---- GET schedules in HUMAN READABLE FORMAT 
SELECT SJ.job_id
    ,SJ.name AS job_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 [Schedule_Description]
INTO #schedules
FROM msdb.dbo.sysjobs SJ
INNER JOIN msdb.dbo.sysjobschedules SJS ON SJ.job_id = SJS.job_id
INNER JOIN msdb.dbo.sysschedules SS ON SJS.schedule_id = SS.schedule_id
option (recompile);

--- Get all details into a temp table with all fancy calculations !! 
SELECT DISTINCT CONVERT(NVARCHAR(128), SERVERPROPERTY('servername')) AS [Server_Name]
    ,trh.job_id AS 'Job_ID'
    ,trh.name AS 'Job_Name'
    ,trh.Last_RunDate AS 'Last_Run_Date'
    ,'Last_RunStatus' = CASE trh.Last_RunStatus
        WHEN 0
            THEN 'Failed'
        WHEN 1
            THEN 'Succeeded'
        WHEN 2
            THEN 'Retry'
        WHEN 3
            THEN 'Canceled'
        WHEN 4
            THEN 'In progress'
        END
    -- MIND BOGGLING CALCULATIONS .... IT will spin your head if you try to decode it !!
    ,'Last_RunDuration (hh:mm:ss)' = cast(trh.Last_RunDuration / 3600 AS VARCHAR(10)) + ':' + replicate('0', 2 - len((trh.Last_RunDuration % 3600) / 60)) + cast((trh.Last_RunDuration % 3600) / 60 AS VARCHAR(2)) + ':' + replicate('0', 2 - len((trh.Last_RunDuration % 3600) % 60)) + cast((trh.Last_RunDuration % 3600) % 60 AS VARCHAR(2))
    ,'Avg_Duration (hh:mm:ss)' = cast(trh.run_duration_avg / 3600 AS VARCHAR(10)) + ':' + replicate('0', 2 - len((trh.run_duration_avg % 3600) / 60)) + cast((trh.run_duration_avg % 3600) / 60 AS VARCHAR(2)) + ':' + replicate('0', 2 - len((trh.run_duration_avg % 3600) % 60)) + cast((trh.run_duration_avg % 3600) % 60 AS VARCHAR(2))
    ,'Max_Duration (hh:mm:ss)' = cast(trh.run_duration_max / 3600 AS VARCHAR(10)) + ':' + replicate('0', 2 - len((trh.run_duration_max % 3600) / 60)) + cast((trh.run_duration_max % 3600) / 60 AS VARCHAR(2)) + ':' + replicate('0', 2 - len((trh.run_duration_max % 3600) % 60)) + cast((trh.run_duration_max % 3600) % 60 AS VARCHAR(2))
    ,'Min_Duration (hh:mm:ss)' = cast(trh.run_duration_min / 3600 AS VARCHAR(10)) + ':' + replicate('0', 2 - len((trh.run_duration_min % 3600) / 60)) + cast((trh.run_duration_min % 3600) / 60 AS VARCHAR(2)) + ':' + replicate('0', 2 - len((trh.run_duration_min % 3600) % 60)) + cast((trh.run_duration_min % 3600) % 60 AS VARCHAR(2))
    ,trh.fromRunDate AS 'From_Date'
    ,trh.Sampling
    ,sched.[Schedule_Description]
INTO #finalReport
FROM #temp_runhistory trh
LEFT JOIN #schedules AS sched ON trh.job_id = sched.job_id
option (recompile)

-- insert back into reporting server (if you have one ...)
--- Generate an AWESOME FINAL REPORT !!
SELECT getdate() AS [Data_Collection_Date]
    ,[Server_Name]
    ,[Job_ID]
    ,[Job_Name]
    ,[Last_Run_Date]
    ,[Last_RunStatus]
    ,[Last_RunDuration (hh:mm:ss)]
    ,[Avg_Duration (hh:mm:ss)]
    ,[Max_Duration (hh:mm:ss)]
    ,[Min_Duration (hh:mm:ss)]
    ,[From_Date]
    ,[Sampling]
    -- we want to get all the schedule for a single JOB with ;** seperated
    ,STUFF((
            SELECT ';** ' + [Schedule_Description]
            FROM #finalReport b
            WHERE b.[Job_Name] = a.[Job_Name]
            FOR XML PATH('')
            ), 1, 3, '') AS [Schedule_Description]
FROM #finalReport a
GROUP BY [Server_Name]
    ,[Job_ID]
    ,[Job_Name]
    ,[Last_Run_Date]
    ,[Last_RunStatus]
    ,[Last_RunDuration (hh:mm:ss)]
    ,[Avg_Duration (hh:mm:ss)]
    ,[Max_Duration (hh:mm:ss)]
    ,[Min_Duration (hh:mm:ss)]
    ,[From_Date]
    ,[Sampling]
ORDER BY [Last_RunDuration (hh:mm:ss)] DESC
option (recompile)
  • 2
    Have a look at this script. It is much more verbose than the one that you came up with. If there is slowness, then you should check the server activity during that time - unless you know that your jobs are causing problem. – Kin Shah Oct 15 '15 at 16:05
2

Here is a query I use to get job information. I have also used SQLJobVis in the past but their website appears to be down at the moment

with jobs as(
select a.name,a.[description], a.enabled,   case c.freq_type  -- Daily, weekly, Monthly
            when 1    then 'Once'
                    when 4    then 'Daily'
                    when 8    then 'Wk ' -- For weekly, add in the days of the week
                    +    case      freq_interval & 2 when 2 then 'M' else '' end  -- Monday
                    +    case      freq_interval & 4 when 4 then 'Tu' else '' end -- Tuesday
                    +    case      freq_interval & 8 when 8 then 'W' else '' end  -- etc
                    +    case      freq_interval & 16 when 16 then 'Th' else '' end
                    +    case      freq_interval & 32 when 32 then 'F' else '' end
                    +    case      freq_interval & 64 when 64 then 'Sa' else '' end
                    +    case      freq_interval & 1 when 1 then 'Su' else '' end
                    when 16   then 'Mthly on day ' + convert(varchar(2), freq_interval) -- Monthly on a particular day
                    when 32   then 'Mthly '  -- The most complicated one, "every third Friday of the month" for example
                    + case c.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 'Every Last '
                    end
                    + case c.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 'Week day'
                        when 10 then 'Weekend day'
                    end 
                when 64   then 'Startup'    -- When SQL Server starts
                when 128 then 'Idle'        -- Whenever SQL Server gets bored
                else 'Err'          -- This should never happen
                end as schedule,
                case c.freq_subday_type     -- FOr when a job funs every few seconds, minutes or hours
                    when 1    then 'Runs once at:'
                    when 2    then 'every ' + convert(varchar(3), freq_subday_interval) + ' seconds'
                    when 4    then 'every ' + convert(varchar(3), freq_subday_interval) + ' minutes'
                    when 8    then 'every ' + convert(varchar(3), freq_subday_interval) + ' hours'
                end as frequency
                ,       substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),c.active_start_time), 6), 1, 2)
                +    ':'
                + substring (
                right (stuff (' ', 1, 1, '000000') + convert(varchar(6), c.active_start_time), 6) ,3 ,2)
                +    ':'
                + substring (
                right (stuff (' ', 1, 1, '000000') + convert(varchar(6),c.active_start_time), 6) ,5 ,2) as start_at

            ,case   c.freq_subday_type
                when 1  then NULL  -- Ignore the end time if not a recurring job
                else    substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6), c.active_end_time), 6), 1, 2)
                +    ':'
                + substring (
                right (stuff (' ', 1, 1, '000000') + convert(varchar(6), c.active_end_time), 6) ,3 ,2)
                +    ':'
                + substring (
                right (stuff (' ', 1, 1, '000000') + convert(varchar(6), c.active_end_time), 6) ,5 ,2) end as end_at
             from msdb.dbo.sysjobs a
            inner join msdb.dbo.sysjobschedules b
            on a.job_id = b.job_id
            inner join msdb.dbo.sysschedules c
            on b.schedule_id = c.schedule_id)

select * from jobs
where start_at between '23:30' and '23:59'
or start_at between '00:00' and '04:30'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.