Before you start
- SQL Server Agent is running (Object Explorer shows a green icon).
- You know what the job does, when it should run and who should hear about failures.
- Database Mail and an operator exist if you want email notifications.
Page 1: General
Object Explorer → SQL Server Agent → Jobs → New Job...
| Field | Recommendation |
|---|---|
| Name | Prefix by purpose: ETL - Load Sales, Maintenance - Stats Update |
| Owner | Not your own login. Use sa (renamed or disabled is fine) or a dedicated login |
| Category | Pick or create one (Database Maintenance, ETL) |
| Description | What it does, who owns it, ticket/reference |
| Enabled | Leave unticked until you've tested, if it's risky |
Page 2: Steps
New... for each step:
- Type: Transact-SQL for most work. PowerShell, Operating system (CmdExec) and SSIS are also available.
- Run as: for non-T-SQL steps, pick a proxy if the step needs specific Windows rights.
- Database: set it. Don't rely on
USEinside the script. - Command: keep it short. Call a stored procedure rather than pasting 300 lines of SQL into the step.
On the step's Advanced page:
| Setting | Recommendation |
|---|---|
| On success action | Go to the next step (or quit with success on the last step) |
| On failure action | Quit the job reporting failure, or go to a clean-up/notify step |
| Retry attempts / interval | 1–3 retries with a few minutes' gap for steps that touch networks or other servers |
| Output file | Set one for long-running or chatty steps; tick Append if you want to keep each run |
| Include step output in history | Tick it, so the message is visible in Job History |
Page 3: Schedules
New... → name the schedule after the timing (Daily 02:30). Check the summary sentence at the bottom of the dialog; it tells you exactly when the job will run. Avoid 01:00–03:00 in daylight-saving time zones for critical jobs.
Page 4: Alerts
Usually empty. Alerts are configured separately (Agent → Alerts) and can start a job, not the other way round.
Page 5: Notifications
Tick E-mail, choose the operator and select When the job fails. Optionally Write to the Windows Application event log on failure, so your monitoring tool picks it up.
Page 6: Targets
Leave as Target local server unless you use multi-server administration.
Test it
Right-click the job → Start Job at Step.... Then View History, expand the run and read each step's message.
Script it out
Right-click the job → Script Job as → CREATE To → New Query Editor Window. Save the script in source control. You now have:
- A way to rebuild the job on a new server.
- A diff when someone changes it later.
Tip
After a few jobs, consider creating them directly in T-SQL from a template. It's faster and consistent (see the T-SQL job template post).
Checklist
- Job owner is not a person
- Category and description set
- Failure flow and retries defined per step
- Output captured in history or file
- Failure notification configured
- Job scripted to source control