Creating SQL Server Agent Jobs for Routine Tasks

2017-12-06 · mssql, automation, tsql

SQL Server Agent is the built-in scheduler, and on a database server it is a better place for backups, index maintenance and cleanup than Windows Task Scheduler or cron: it runs under the service account, logs history in msdb, can send alerts through Database Mail, and its jobs travel with your scripted instance configuration. I used to build every job by clicking through SSMS, right up until I had to recreate the same one on ten servers. Now I write them in T-SQL so they can be checked in and deployed.

The example is a nightly cleanup job with two steps, a schedule and a failure email. You need Standard or Enterprise (Express has no Agent), the Agent service set to Automatic, and sysadmin or SQLAgentOperatorRole in msdb. On Linux with SQL Server 2017, install mssql-server-agent and restart mssql-server. Database Mail has to be configured if you want notifications.

1. Create an operator

An operator is a name plus an email address that alerts can be routed to:

USE msdb;
EXEC dbo.sp_add_operator
    @name = N'DBA Team',
    @enabled = 1,
    @email_address = N'dba-alerts@example.com';

2. Create the job

EXEC dbo.sp_add_job
    @job_name = N'Nightly - ShopDB cleanup',
    @enabled = 1,
    @description = N'Purge old session rows and update statistics',
    @owner_login_name = N'sa',
    @notify_level_email = 2,        -- 2 = on failure
    @notify_email_operator_name = N'DBA Team';

I always set the owner to sa (or another sysadmin login), because jobs owned by a person break the day that person leaves and their login is dropped. If the owner is not a sysadmin, T-SQL steps run under that login's permissions.

3. Add the job steps

Each step has a subsystem: TSQL, CmdExec, PowerShell, SSIS and so on. The @on_success_action and @on_fail_action values control flow: 1 quit with success, 2 quit with failure, 3 go to next step, 4 go to a specific step.

EXEC dbo.sp_add_jobstep
    @job_name = N'Nightly - ShopDB cleanup',
    @step_name = N'Purge sessions older than 30 days',
    @step_id = 1,
    @subsystem = N'TSQL',
    @database_name = N'ShopDB',
    @command = N'DELETE TOP (50000) FROM dbo.Sessions WHERE LastSeen < DATEADD(DAY, -30, GETDATE());
WHILE @@ROWCOUNT = 50000
    DELETE TOP (50000) FROM dbo.Sessions WHERE LastSeen < DATEADD(DAY, -30, GETDATE());',
    @retry_attempts = 2,
    @retry_interval = 5,           -- minutes
    @on_success_action = 3,
    @on_fail_action = 2;

EXEC dbo.sp_add_jobstep
    @job_name = N'Nightly - ShopDB cleanup',
    @step_name = N'Update statistics',
    @step_id = 2,
    @subsystem = N'TSQL',
    @database_name = N'ShopDB',
    @command = N'EXEC sp_updatestats;',
    @on_success_action = 1,
    @on_fail_action = 2;

Deleting in batches keeps the transaction log from ballooning and avoids a table-level lock for the whole run.

4. Schedule it

EXEC dbo.sp_add_schedule
    @schedule_name = N'Daily 02:30',
    @freq_type = 4,                 -- daily
    @freq_interval = 1,             -- every 1 day
    @active_start_time = 023000;    -- HHMMSS

EXEC dbo.sp_attach_schedule
    @job_name = N'Nightly - ShopDB cleanup',
    @schedule_name = N'Daily 02:30';

Schedules are separate objects and can be shared between jobs, which is why the attach step exists. The @freq_type codes are worth memorising, since you'll look them up constantly otherwise: 4 daily, 8 weekly (with @freq_interval as a bitmask of days), 16 monthly, 64 when Agent starts.

5. Register the job with the server

I forget this one every time I script a job, and without it the job never runs:

EXEC dbo.sp_add_jobserver
    @job_name = N'Nightly - ShopDB cleanup',
    @server_name = N'(local)';

Run it now

Don't wait for 02:30:

EXEC msdb.dbo.sp_start_job N'Nightly - ShopDB cleanup';

Then read the history, which is stored per step with step 0 being the job outcome:

SELECT j.name, h.step_id, h.step_name, h.run_status, h.run_date, h.run_time,
       h.run_duration, h.message
FROM msdb.dbo.sysjobhistory h
JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
WHERE j.name = N'Nightly - ShopDB cleanup'
ORDER BY h.instance_id DESC;

run_status 1 is succeeded, 0 failed, 3 cancelled. The message column contains the actual error text from a failed step, which is far more useful than the summary SSMS shows.

Problems I've hit

Job runs but nothing happens. The step's @database_name defaults to master. If your command uses unqualified table names, it is running in the wrong database. Set the database on every step.

"The job failed. Unable to determine if the owner (CORP\jsmith) of job ... has server access." The owner login was disabled or removed from the domain. Change the owner with EXEC msdb.dbo.sp_update_job @job_name = N'...', @owner_login_name = N'sa'.

History disappears after a day. Agent keeps only 1000 rows total and 100 per job by default. Raise it in SQL Server Agent Properties, History, or with sp_set_sqlagent_properties @jobhistory_max_rows = 10000, @jobhistory_max_rows_per_job = 1000.

Email notification never arrives. Database Mail must be enabled for Agent specifically: SQL Server Agent Properties, Alert System, tick Enable mail profile and choose the profile, then restart the Agent service. Check msdb.dbo.sysmail_faileditems for the actual SMTP error.

I use SSMS only to look at jobs now. Everything gets created from a script.