SQL Server – SQL Server Agent – Scheduled Jobs

Introduction

SQL Server jobs usually runs when, you schedule them and one usually forgets them. But, ever so often, the end user community cries out and says there are no data.

And, then one is forced to look at the jobs and tries to find those little potholes.

SQL

Here is a bit of working code to list our scheduled jobs and tried to iterate the time they are scheduled to run.


MS SQL Server – v2000


SELECT
	     tblJob.job_id as jobID
	   , tblJob.name as jobName
           , tblJob.enabled as isJobEnabled
	   , tblJob.category_id as jobCategoryID
	   , tblCategory.name as jobCategoryLiteral
	   , tblJob.owner_sid as jobOwnerSID
	   , suser_sname(tblJob.owner_sid) as jobOwnerSIDLiteral
	   , tblJob.date_created as jobDateCreated
	   , tblJob.date_modified as jobDateModified
	   , tblJobSchedule.schedule_id as jobScheduleID
	   , tblJobSchedule.name as jobScheduleName
	   , tblJobSchedule.[enabled] as jobScheduleEnabled
	   , tblJobSchedule.freq_type as jobFrequencyTypeID
	   , case tblJobSchedule.freq_type
		when 1 then 'Once'
		when 4 then 'Daily'
		when 8 then 'Weekly'
		when 16 then 'Monthly'
		when 32 then 'Monthly'
		when 64 then 'Runs when the SQL Server Agent service starts'
		when 128 then 'Runs when the computer is idle'
	     end as jobFrequencyIntervalLiterval

	   , tblJobSchedule.freq_interval as jobFrequencyIntervalID
           , tblJobSchedule.active_start_time as jobStartTime

	   , case datalength(cast(tblJobSchedule.active_start_time as varchar(30)))

	       when 6 then 
		  cast(cast(substring(cast(tblJobSchedule.active_start_time as varchar(6)), 1,2) as int) - 12 as varchar)
		+ ':'
		+ substring(cast(tblJobSchedule.active_start_time as varchar(6)), 3,2) 
		+ ':'
		+ substring(cast(tblJobSchedule.active_start_time as varchar(6)), 5,2) 
		+ ' PM'

   	     when 5 then 
		  substring(cast(tblJobSchedule.active_start_time as varchar(5)), 1,1) 
		  + ':'
		  + substring(cast(tblJobSchedule.active_start_time as varchar(5)), 2,2)   		  + ':'
		  + substring(cast(tblJobSchedule.active_start_time as varchar(5)), 4,2) 		  + ' AM'

	    when 4 then 
		  cast(cast(substring(cast(tblJobSchedule.active_start_time as varchar(6)), 1,2) as int) - 12 as varchar)
		+ ':'
		+ substring(cast(tblJobSchedule.active_start_time as varchar(4)), 3,2) 
		+ ' PM'

	when 3 then 
		 substring(cast(tblJobSchedule.active_start_time as varchar(3)), 1,1) 
		+ ':'
		+ substring(cast(tblJobSchedule.active_start_time as varchar(3)), 2,2) 

	when 2 then 
		  substring(cast(tblJobSchedule.active_start_time as varchar(2)), 1,2) 

	when 1 then 
	   case substring(cast(tblJobSchedule.active_start_time as varchar(1)), 1,1) 
                when '0' then '12:00 AM'
                else '12:0'
                  + substring(cast(tblJobSchedule.active_start_time as varchar(1)), 1,1) 		  + ' AM'
	  end

  end as jobStartTimeLiteral	

FROM msdb.dbo.sysjobs tblJob

	LEFT OUTER JOIN msdb.dbo.sysJobschedules tblJobSchedule

		ON tblJob.Job_ID = tblJobSchedule.Job_id

	LEFT OUTER JOIN msdb.dbo.syscategories tblCategory

		ON tblJob.category_id = tblCategory.category_id

/*
	LEFT OUTER JOIN msdb.dbo.sysschedules tblJobSchedule

		ON tblJobSchedule.schedule_id = tblJobSchedule.schedule_id
*/

order by 
	tblJob.name


MS SQL Server – v2005, v2008, v2012




use [msdb]
go
 
SELECT
         tblJob.job_id as jobID
       , tblJob.name as jobName
       , tblJob.[enabled] as isJobEnabled
       , tblJob.category_id as jobCategoryID
       , tblCategory.name as jobCategoryLiteral
       , tblJob.owner_sid as jobOwnerSID
       , suser_sname(tblJob.owner_sid) as jobOwnerSIDLiteral
       , tblJob.date_created as jobDateCreated
       , tblJob.date_modified as jobDateModified
       , tblJobSchedule.schedule_id as jobScheduleID
       , tblSchedule.name as jobScheduleName
       , tblSchedule.[enabled] as jobScheduleEnabled
       , tblSchedule.freq_type as jobFrequencyTypeID
       , case tblSchedule.freq_type
        when 1 then 'Once'
        when 4 then 'Daily'
        when 8 then 'Weekly'
        when 16 then 'Monthly'
        when 32 then 'Monthly'
        when 64 then 'Runs when the SQL Server Agent service starts'
        when 128 then 'Runs when the computer is idle'
         end as jobFrequencyIntervalLiterval
 
       , tblSchedule.freq_interval as jobFrequencyIntervalID
           , tblSchedule.active_start_time as jobStartTime
 
       , case datalength(cast(tblSchedule.active_start_time as varchar(30)))
            when 6 then
 
                case
 
                    when substring(cast(tblSchedule.active_start_time as varchar(6)), 1,2) < 12 then
 
                        cast(cast(substring(cast(tblSchedule.active_start_time as varchar(6)), 1,2) as int) - 0 as varchar)
                        + ':'
                        + substring(cast(tblSchedule.active_start_time as varchar(6)), 3,2) 
                        + ':'
                        + substring(cast(tblSchedule.active_start_time as varchar(6)), 5,2) 
                        + ' AM'
 
                    else
                           cast(cast(substring(cast(tblSchedule.active_start_time as varchar(6)), 1,2) as int) - 12 as varchar)
                        + ':'
                        + substring(cast(tblSchedule.active_start_time as varchar(6)), 3,2) 
                        + ':'
                        + substring(cast(tblSchedule.active_start_time as varchar(6)), 5,2) 
                        + ' PM'
 
                end
 
         when 5 then
          substring(cast(tblSchedule.active_start_time as varchar(5)), 1,1) 
          + ':'
          + substring(cast(tblSchedule.active_start_time as varchar(5)), 2,2) 
		  --+ ':'
          --+ substring(cast(tblSchedule.active_start_time as varchar(5)), 4,2)    
		  + ' AM'
 
		/*
        when -4 then
          cast(cast(substring(cast(tblSchedule.active_start_time as varchar(6)), 1,2) as int) - 12 as varchar)
        + ':'
        + substring(cast(tblSchedule.active_start_time as varchar(4)), 3,2) 
        + ' PM'
		*/

        when 4 then
        + '00:'
        + substring(cast(tblSchedule.active_start_time as varchar(6)), 1,2) 
        --+ ':'
        --+ substring(cast(tblSchedule.active_start_time as varchar(6)), 3,2) 
        + ' AM'

    when 3 then
         substring(cast(tblSchedule.active_start_time as varchar(3)), 1,1) 
        --+ ':'
        --+ substring(cast(tblSchedule.active_start_time as varchar(3)), 2,2) 
        + ' AM'

    when 2 then
          substring(cast(tblSchedule.active_start_time as varchar(2)), 1,2) 
 
    when 1 then
       case substring(cast(tblSchedule.active_start_time as varchar(1)), 1,1) 
            when '0' then '12:00 AM'
            else '12:0'
                  + substring(cast(tblSchedule.active_start_time as varchar(1)), 1,1) 
				  + ' AM'
      end
 
  end as jobStartTimeLiteral    
 
FROM msdb.dbo.sysjobs tblJob
 
    LEFT OUTER JOIN msdb.dbo.sysJobschedules tblJobSchedule
 
        ON tblJob.Job_ID = tblJobSchedule.Job_id
 
    LEFT OUTER JOIN msdb.dbo.syscategories tblCategory
 
        ON tblJob.category_id = tblCategory.category_id
 
    LEFT OUTER JOIN msdb.dbo.sysschedules tblSchedule
 
        ON tblJobSchedule.schedule_id = tblSchedule.schedule_id
 
where tblJob.[enabled] = 1

and   tblSchedule.[enabled] = 1

and   tblCategory.name not like 'Log Shipping'

and   tblCategory.name not in
		(
			  'REPL-Checkup'	
			, 'REPL-History Cleanup'
			, 'REPL-Distribution Cleanup'
		)
order by
    tblJob.name

Listening To…

Wanted “De la Soul” potholes in my Lawn; but instead will reference “Arrested Developments’s Mr Wendal ( http://www.youtube.com/watch?v=wyDjRd0Tjss ).

References

References – System Tables

References – Sample Code

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s