Simple example for creating and scheduling SQL Server Agent jobs

A table

We create a table with one column only: dt_insert. When the job executes, it will insert the timestamp of its execution into this table.

create table tq84_job_example_tab (
   dt_insert datetime2
);

Github repository about-MSSQL, path: /architecture/services/agent/jobs/examples/simple/table.sql

Stored procedure

The following stored procedure will be called by our job. It contains the insert statement that creates the record in tq84_job_example_tab.

create procedure tq84_job_example_proc as
   insert into tq84_job_example_tab(dt_insert) values (sysdatetime())
go

Github repository about-MSSQL, path: /architecture/services/agent/jobs/examples/simple/insert-proc.sql

Add the job

A job is created with msdb.dbo.sp_add_job.

exec msdb.dbo.sp_add_job @job_name = N'tq84_job_example_job';

Github repository about-MSSQL, path: /architecture/services/agent/jobs/examples/simple/add-job.sql

In order to add a job, The SQL Server Agent services needs to be running. This can be verified with

exec master.sys.xp_servicecontrol N'querystate', N'SQLserverAgent'

If the service is found to be stopped, it can be started with

exec master.sys.xp_servicecontrol N'start', N'SQLserverAgent'

After a job was added, the job is shown in Management Studio (SSMS) in the Object Explorer under SQL Server Agent -> Jobs.

Add one or more job steps

A job by itself doesn't do much. It is a container of job steps. It's these job steps that actually define what will be executed.

declare
    @db_name sysname = db_name();
exec msdb..sp_add_jobstep
    @job_name          = N'tq84_job_example_job',
    @step_name         = N'tq84_job_example_step',
    @step_id           =   1,  -- Used to order steps.
    @subsystem         = N'TSQL',
    @database_name     =  @db_name,
    @command           = N'exec tq84_job_example_proc',
    @on_success_action =   1, -- 1:...  /  3: Go to next step (3 should not be the value of the last step)
    @retry_attempts    =   0,
    @retry_interval    =   0
;

Github repository about-MSSQL, path: /architecture/services/agent/jobs/examples/simple/add-jobstep.sql

Add a schedule

A schedule defines when, how often and which periods etc. a job is executed. Unfortunately, it's quite unintuitive, imho, how such a schedule is defined:

exec msdb.dbo.sp_add_schedule  
    @schedule_name        = N'tq84_job_example_schedule',
    @freq_type            =   4,       -- 4: daily
    @freq_interval        =   4,       -- 4: daily
    @freq_subday_type     =   2,       -- 2: seconds
    @freq_subday_interval =  30,       -- Every 30 seconds.
    @active_start_time    =  null      -- start immediately
--  @active_end_time      = 
;

Github repository about-MSSQL, path: /architecture/services/agent/jobs/examples/simple/add-schedule.sql

Attach the job to the schedule

A schedule can be used for multiple jobs. So, a job needs to be attached to a schedule:

exec msdb.dbo.sp_attach_schedule
    @job_name      = N'tq84_job_example_job',
    @schedule_name = N'tq84_job_example_schedule';

Github repository about-MSSQL, path: /architecture/services/agent/jobs/examples/simple/attach-schedule.sql

Add a job execution server

We need to specify a server on which the jobs are run:

exec msdb.dbo.sp_add_jobserver
   @job_name    = N'tq84_job_example_job',
   @server_name = @@servername

Github repository about-MSSQL, path: /architecture/services/agent/jobs/examples/simple/add-jobserver.sql

Query information about the job

select
   job.enabled,
   --
   job.date_created            date_job_created,
   job.date_modified           date_job_modified,
   --
   next_scheduled_run_date,
   datediff(ss, getdate(), next_scheduled_run_date) seconds_from_now,
   --
   act.session_id,
   act.run_requested_date,
   act.run_requested_source,
   act.start_execution_date,
   act.last_executed_step_id,
   act.last_executed_step_date,
   act.stop_execution_date,
   act.job_history_id,
   job.notify_level_eventlog,
-- job.owner_sid               job_owner_sid,
   own.name                    job_owner_name,
   --
   srv.server_id,
   srv.last_run_outcome,
   srv.last_run_date,
   srv.last_run_time,
   srv.last_run_duration,
   srv.last_outcome_message
from
   msdb.dbo.sysjobs          job                                 left join
   msdb.dbo.sysjobactivity   act on job.job_Id = act.job_id      left join
   master.sys.syslogins      own on job.owner_sid = own.sid      left join
   msdb.dbo.sysjobservers    srv on job.job_id    = srv.job_id
where
   job.name = 'tq84_job_example_job'
;

Github repository about-MSSQL, path: /architecture/services/agent/jobs/examples/simple/query-job-info.sql

Query job history

select
   his.run_date,
   his.run_time,
   his.run_duration,
   his.instance_id,
   his.step_id,
   his.step_name,
   his.run_status,
   his.sql_message_id,
   his.sql_severity,
   his.message,
   his.server
from
   msdb.dbo.sysjobs          job                             left join
   msdb.dbo.sysjobhistory    his on job.job_id = his.job_id
where
   job.name = 'tq84_job_example_job'
order by
   his.instance_id desc
;

Github repository about-MSSQL, path: /architecture/services/agent/jobs/examples/simple/query-job-history.sql

Qyery job steps

Some information about job steps is stored in msdb.dbo.sysjobsteps:

select
   stp.step_uid,
   stp.step_id,
   stp.step_name,
   stp.subsystem,
   stp.additional_parameters,
   stp.database_name           step_database,
   stp.retry_attempts,
   stp.retry_interval,
   stp.last_run_date    last_run_date_step,
   stp.last_run_time    last_run_time_step,
   case stp.on_success_action when 3 then 'go on with next step' end on_success_acction
from
   msdb..sysjobs      job                              left join --  created jobs
   msdb..sysjobsteps  stp on job.job_id = stp.job_id
where
   job.name = 'tq84_job_example_job'
order by
   stp.step_id;

Github repository about-MSSQL, path: /architecture/services/agent/jobs/examples/simple/query-job-steps.sql

Clean up

exec msdb..sp_delete_jobserver
    @job_name = N'tq84_job_example_job',
    @server_name = @@servername

exec msdb..sp_delete_jobstep 
   @step_id  =  1,
   @job_name = 'tq84_job_example_job';

exec msdb..sp_delete_job @job_name = N'tq84_job_example_job';
   
exec msdb..sp_delete_schedule @schedule_name = N'tq84_job_example_schedule'

Github repository about-MSSQL, path: /architecture/services/agent/jobs/examples/simple/clean-up.sql

TODO

exec msdb..sp_help_job @job_name = 'tq84_job_example_job', @job_aspect = 'job'
exec msdb..sp_help_job @job_name = 'tq84_job_example_job', @job_aspect = 'schedules'
exec msdb..sp_help_job @job_name = 'tq84_job_example_job', @job_aspect = 'steps'
exec msdb..sp_help_job @job_name = 'tq84_job_example_job', @job_aspect = 'targets'