A Closer Look at SQL Server Agent Job Scheduling

My favorite feature of Microsoft SQL Server is the SQL Server Agent. It was the only reason I was able to succeed as the only DBA in the medical system where I started my journey into data. Why? Because SQL Server Agent allows you to create a virtual team of DBAs to work when you can’t – i.e. 24 hours per day, seven days per week. It allows you to schedule jobs to fire on any schedule you can conceive and as the SQL services come online.

So let’s walk through to configure SQL Agent job schedules, how to identify jobs that share the same schedules – because that can happen – and what I recommend as some of the best practices around job scheduling.

How to Configure SQL Agent Job Schedules

While it’s completely possible to schedule SQL Server Agent jobs via Transact-SQL using sp_ having to memorize the numerical equivalents of parameters controlling everything from day of week, time of day, and recurrence values make that option beyond challenging. That is why I strongly recommend sticking to the GUI in SQL Server Management Studio (SSMS) for that task.

To add a schedule to a SQL Server Agent job using the GUI connect to a SQL instance in SSMS Object Explorer then expand SQL Server Agent then Jobs. This will give you a listing of all Agent jobs on the SQL Server instance.

Right click the job you wish to add a schedule to and select Properties from the available options. You’ll be greeted with a dialog box to which you’ll want to navigate to the Schedule tab.

Now create an initial schedule for a job. Select New from the available options at the bottom of the dialog and you’ll be presented with the New Job Schedule dialog box. This is where you’ll complete the following criteria needed to create a job schedule:

Name

This is the name of the job schedule – not the job name. What I advocate for doing – and this is the main point I wanted to make with so-called “best practice” is that if you don’t ever intend to share a job schedule amongst multiple jobs then stick to a naming convention of schedule name = job name. If you’ve multiple schedules for a specific job then use the job name as a prefix as in “job_name_schedule01”, “job_name_schedule02”, etc.

I’ve seen situations like this: Jobs are created from scripts on one SQL Server instance where a schedule is shared amongst more than one job or where the unique identifier for a schedule already exists on an instance of SQL Server and is referenced for a new job. Later, there is a need to change the schedule for a job for some reason. As a result, any job sharing that schedule now is also affected whether intentionally or not. We’ll look at identifying jobs sharing schedules at the end of this article.

Schedule Type

Job schedules can be either recurring or one time only. While you’ll normally configure recurring schedules for most of your jobs, sometimes you’ll find yourself scheduling single-use only jobs for instances where you need to perform an action but it conflicts with your time away from your computer. Many a go-live was performed by one of my SQL Agent Jobs in the wee hours of the morning so I would not need to be up just to kick off a one-time backup or run a t-sql script before other work is performed by different teams.

Rarer are the options for kicking off a job on service startup or rarer still, when the CPU for the SQL instance becomes idle. I’ve never had to utilize that schedule but would love to see comments from readers where they may have used that option.

Enabled

This is where you state whether the schedule itself is enabled or not. Keep in mind that this is one of those “gotcha” configurations. You can have a job enabled with a schedule that is disabled and visa-versa. Either case results in a job not running at the scheduled time. Bottom line: If you want a job to run on a specific schedule both the job and the schedule need to be enabled.

Frequency

Frequency in this section specifically refers to whether the schedule is going to be either Daily, Weekly, or Monthly. If either of the last two options are selected you’re then able to select the day or days of the week or month that apply. A Daily schedule selection allows you to set the frequency of the daily recurrence be it daily, every other day, and so forth.

Daily Frequency

This section’s title is misleading since it applies to schedules that are not just daily but also weekly or monthly. Essentially, it’s the setting for how frequent a job will run at any given measure be it once-per-day or some modification on that: hourly, every x minutes, etc.

Duration

Duration dictates when the schedule is slated to kick in as a starting date as well as finish for an ending date. In many cases you’ll want to set the start date to be the current date and a fictional end date since you’ll not likely want jobs to stop running unless you need to go in and manually change the job to disabled for purpose of maintenance or some other temporary pausing of a job. However I’ve made use of these settings for jobs that collect metadata from Dynamic Management Objects for monitoring or to run a simulated load while doing development work for a distinct period of time.

Why GUI v. Code?

Based upon the information above I want to give reason to why I’m advocating for using the GUI versus coding the schedule creation.

Let’s say a job schedule is set to execute each day at 11:11:11am starting on 8/1/2017. I’ve set the schedule to disabled since this is just a test.

If I were not using a GUI, I’d need to make the following call to the following system stored procedure in the msdb database which stores all metadata related to the SQL Server Agent job system:

EXEC msdb.dbo.sp_add_jobschedule

        @job_name=N'This Job Shares a Schedule',

        @name=N'This Job Shares a Schedule Schedule_02',

        @enabled=0,

        @freq_type=4,

        @freq_interval=1,

        @freq_subday_type=1,

        @freq_subday_interval=0,

        @freq_relative_interval=0,

        @freq_recurrence_factor=1,

        @active_start_date=20170801,

        @active_end_date=99991231,

        @active_start_time=111111,

        @active_end_time=235959;

Unless you were to look up the stored procedure in Books Online you’d likely not be able to memorize the associated parameter values corresponding to what is so user-friendly in SSMS.

Identifying Jobs Sharing the Same Schedule

I mentioned earlier that there may be situations that arise that may lead to more than a single job sharing a schedule. There is nothing wrong with that and this configuration can be valuable if you have multiple jobs that are part of a larger event that need to execute on the same cadence. My experience has seen when this can cause problems though due to unintended circumstances of making a change for a single job that ultimately results in changes to multiple jobs.

Jobs are related to schedules through a many-to-many relationship: one job can have multiple schedules and a schedule can be assigned to multiple jobs. The many-to-many relationship is maintained through an intermediate join between the necessary System Views.

Job metadata is contained in the msdb.dbo.sysjobs System View whereas all schedules for a SQL Server instance can be observed through the msdb.dbo.sysschedules System View. Seasoned DBAs will note that the schema for these system objects is dbo – not sys – as is the standard. This is because the msdb database has not effectively changed much since the days of SQL Server 7.0 and all the old naming conventions remain in place.

Using this join scheme I want to build towards identifying jobs with shared schedules. We’ve accomplished the first step in this process when we identified the join of the three necessary System Views above. The next step is to return a listing of jobs and their schedules:

SELECT JOBS.name AS job_name

        , SCHEDS.name AS schedule_name

FROM msdb.dbo.sysjobs AS JOBS

        INNER JOIN msdb.dbo.sysjobschedules AS JOBS_SCHEDS

               ON JOBS_SCHEDS.job_id = JOBS.job_id

        INNER JOIN msdb.dbo.sysschedules AS SCHEDS

               ON SCHEDS.schedule_id = JOBS_SCHEDS.schedule_id

ORDER BY SCHEDS.name, JOBS.name;

As you may notice I aliased the System View names to make the code a bit more user-friendly

Now while this is a short list and all the jobs ordered out to identify shared schedules that is likely not going to be the case in a production environment. For that I’d take the final step to only return jobs with shared schedules using aggregation clauses like below:

SELECT SHARED_SCHEDS.schedule_name

        , JOBS.name AS job_name 

FROM msdb.dbo.sysjobs AS JOBS

        INNER JOIN msdb.dbo.sysjobschedules AS JOBS_SCHEDS

               ON JOBS_SCHEDS.job_id = JOBS.job_id

        INNER JOIN

               (

               SELECT SCHEDS.schedule_id, SCHEDS.name AS schedule_name

               FROM msdb.dbo.sysjobschedules AS JOBS_SCHEDS

                       INNER JOIN msdb.dbo.sysschedules AS SCHEDS

                               ON SCHEDS.schedule_id = JOBS_SCHEDS.schedule_id

               GROUP BY SCHEDS.schedule_id, SCHEDS.name

               HAVING COUNT(JOBS_SCHEDS.job_id) > 1

               ) AS SHARED_SCHEDS

                       ON SHARED_SCHEDS.schedule_id = JOBS_SCHEDS.schedule_id

ORDER BY SHARED_SCHEDS.schedule_name, JOBS.name;

I’m sure you’ll notice that I’ve grouped on both schedule_id and schedule name. This is only necessary because I want to be able to return the schedule name to the result set and I could accomplish that through this process or by adding another INNER JOIN to msdb.dbo.sys_schedules from msdb.dbo.sysjobschedules outside of the aggregation subquery as shown here:

SELECT SCHEDS.name AS schedule_name

        , JOBS.name AS job_name 

FROM msdb.dbo.sysjobs AS JOBS

        INNER JOIN msdb.dbo.sysjobschedules AS JOBS_SCHEDS

               ON JOBS_SCHEDS.job_id = JOBS.job_id

        INNER JOIN

               (

               SELECT SCHEDS.schedule_id

               FROM msdb.dbo.sysjobschedules AS JOBS_SCHEDS

                       INNER JOIN msdb.dbo.sysschedules AS SCHEDS

                               ON SCHEDS.schedule_id = JOBS_SCHEDS.schedule_id

               GROUP BY SCHEDS.schedule_id

               HAVING COUNT(JOBS_SCHEDS.job_id) > 1

               ) AS SHARED_SCHEDS

                       ON SHARED_SCHEDS.schedule_id = JOBS_SCHEDS.schedule_id

        INNER JOIN msdb.dbo.sysschedules AS SCHEDS

               ON SCHEDS.schedule_id = SHARED_SCHEDS.schedule_id

ORDER BY SCHEDS.name, JOBS.name;

 This was the cleaner of the two processes. Out of curiosity I did take the time to run both queries in my sandbox environment and the first version was slightly less costly in addition to appearing cleaner to me.

Scripting Jobs

Microsoft makes it quite easy to script jobs for migration to other servers. There is one change I always make to the script that is generated by this process and it involves job scheduling and the concern mentioned earlier about the uniqueness of the schedule_id.

Below is the script associated with a job creation. This is easily generated by right-clicking any job in the SSMS Object Explorer and selecting Script Job as, followed by CREATE To, followed by New Query Window. I’ve not changed the formatting below but depending on your version of SQL it may look slightly different.

USE [msdb]

GO

/****** Object:  Job [_Cycle Error Log]    Script Date: 7/31/2017 4:04:17 PM ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [Administrative Job: Custom]    Script Date: 7/31/2017 4:04:17 PM ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Administrative Job: Custom' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Administrative Job: Custom'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'_Cycle Error Log',

               @enabled=1,

               @notify_level_eventlog=0,

               @notify_level_email=0,

               @notify_level_netsend=0,

               @notify_level_page=0,

               @delete_level=0,

               @description=N'exec master.sys.sp_cycle_errorlog',

               @category_name=N'Administrative Job: Custom',

               @owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [cycle errorlog]    Script Date: 7/31/2017 4:04:17 PM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'cycle errorlog',

               @step_id=1,

               @cmdexec_success_code=0,

               @on_success_action=3,

               @on_success_step_id=0,

               @on_fail_action=2,

               @on_fail_step_id=0,

               @retry_attempts=0,

               @retry_interval=0,

               @os_run_priority=0, @subsystem=N'TSQL',

               @command=N'EXEC sp_cycle_errorlog;',

               @database_name=N'master',

               @output_file_name=N'\TIMFLAPTOPC$DataMSSQL12.MSSQLSERVERMSSQLLOGcycle_error_log.log',

               @flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [cycle agent error log]    Script Date: 7/31/2017 4:04:17 PM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'cycle agent error log',

               @step_id=2,

               @cmdexec_success_code=0,

               @on_success_action=1,

               @on_success_step_id=0,

               @on_fail_action=2,

               @on_fail_step_id=0,

               @retry_attempts=0,

               @retry_interval=0,

               @os_run_priority=0, @subsystem=N'TSQL',

               @command=N'EXEC sp_cycle_agent_errorlog;',

               @database_name=N'msdb',

               @output_file_name=N'\TIMFLAPTOPC$DataMSSQL12.MSSQLSERVERMSSQLLOGcycle_error_log.log',

               @flags=2

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Biweekly',

               @enabled=1,

               @freq_type=8,

               @freq_interval=64,

               @freq_subday_type=1,

               @freq_subday_interval=0,

               @freq_relative_interval=0,

               @freq_recurrence_factor=2,

               @active_start_date=20100121,

               @active_end_date=99991231,

               @active_start_time=33333,

               @active_end_time=235959,

               @schedule_uid=N'9a276f65-edde-4412-add9-29388bbc05af'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

The code creates the job category if it doesn't exist, creates the job, and then creates and assigns the job schedule. It’s that last step, creating and assigning the schedule that I tweak but just one parameter.

The last line of code for the msdb.dbo.sp_add_jobschedule stored procedure sets the schedule_id to be what is utilized in the existing instance of SQL Server that you’ve scripted the job from. I always remove that entire parameter assignment (and the preceding comma of course to avoid the inevitable syntax error that would result. This ensures that a new schedule_id GUID will be assigned to this schedule and that it won’t run the risk of being a duplicated/shared schedule if there is a GUID clash on the new instance.

Conclusion

In a nutshell this is the process by which to schedule SQL Server Agent jobs and check for shared schedules. Remember that unless both the job and the job schedule are enabled your job will not run. (Trust me, I’ve been burned by this in the past.) Also be on the lookout for issues that may arise if a schedule is assigned to multiple jobs.