Feb 11, 2011

Script To know Jobs Scheduled

-- to know what jobs were schedule for the next 24 hours
CREATE PROC dbo.GetJobsScheduledInTheNextNHours (@HowManyHoursAhead INT = 24)
AS
WITH OurJobs AS (
    SELECT  job.job_id,  job.[name]
      , CASE job.[description] WHEN 'No description available.' THEN NULL ELSE job.description END AS Description
      , job.date_modified
      , CASE sched.next_run_date
            WHEN 0 THEN 'Never'
            ELSE
              CONVERT(varchar(10), CONVERT(smalldatetime, CAST(sched.next_run_date as varchar), 120), 120)+' '+
              RIGHT('0'+CAST((sched.next_run_time/10000) AS VARCHAR), 2)+':'+
              RIGHT('0'+CAST((sched.next_run_time-((sched.next_run_time/10000)*10000))/100 AS VARCHAR), 2)+':'+
              RIGHT('0'+CAST((sched.next_run_time-((sched.next_run_time/10000)*10000)-((sched.next_run_time-((sched.next_run_time/10000)*10000))/100*100)) AS VARCHAR), 2)
      END AS NextRunDateTime
      , (
        SELECT CASE last_run_date
            WHEN 0 THEN 'Never'
            ELSE
              CONVERT(varchar(10), CONVERT(smalldatetime, CAST(last_run_date as varchar), 120), 120)+' '+
              RIGHT('0'+CAST((last_run_time/10000) AS VARCHAR), 2)+':'+
              RIGHT('0'+CAST((last_run_time-((last_run_time/10000)*10000))/100 AS VARCHAR), 2)+':'+
              RIGHT('0'+CAST((last_run_time-((last_run_time/10000)*10000)-((last_run_time-((last_run_time/10000)*10000))/100*100)) AS VARCHAR), 2)
          END AS LastRunDateTime
        FROM msdb.dbo.sysjobsteps
        WHERE job_id = job.job_id AND step_id = (
          SELECT MAX(step_id)
          FROM msdb.dbo.sysjobsteps
          WHERE job_id = job.job_id
        )
      ) as LastSuccessfulExecution
    FROM msdb.dbo.sysjobs job JOIN msdb.dbo.sysjobschedules sched
        ON sched.job_id = job.job_id
    WHERE job.enabled = 1 -- remove this if you wish to return all jobs
        AND sched.next_run_date > 0
)
SELECT * FROM OurJobs
WHERE DATEDIFF(hh, GETDATE(), NextRunDateTime) <= @HowManyHoursAhead
GO
EXEC GetJobsScheduledInTheNextNHours 24

No comments:

Post a Comment

Hi,

Thanks for your visit to this blog.
We would be happy with your Queries/Suggestions.