SQL Server Agent questions every DBA gets asked: 20 short answers

#Basics

1. Where are jobs stored? In the msdb database: dbo.sysjobs, sysjobsteps, sysschedules, sysjobschedules and sysjobhistory. Back up msdb, or you lose every job.

2. Does SQL Server Express have Agent? No. Use Windows Task Scheduler with sqlcmd, or a scheduler in another tool.

3. Does Azure SQL Database have Agent? No. Use Elastic Jobs, Azure Automation or Data Factory. Azure SQL Managed Instance does have SQL Agent, with some differences (no CmdExec or PowerShell subsystems; check current docs).

4. Which account does a job step run as?

• T-SQL steps run as the job owner. If the owner is sysadmin, they run as the Agent service account's connection.
• CmdExec, PowerShell and SSIS steps run as the Agent service account, or as a proxy if you pick one.

5. Who should own jobs? A disabled SQL login or a dedicated account, not a person. A job owned by an employee who leaves can fail when their AD account is disabled.

#Scheduling

6. Why didn't my job run? Check that the job and the schedule are enabled and that Agent was running at that time. Look at next_run_date in sysjobschedules. Also check whether the previous run was still going: Agent never starts a second instance of the same job.

7. Which time zone do schedules use? The server's local time. Daylight-saving changes can skip or repeat runs between 01:00 and 03:00. Schedule critical jobs outside that window.

8. Can one schedule be shared by several jobs? Yes (sp_attach_schedule). Changing it changes every attached job, which surprises people. Prefer one schedule per job.

9. What is the shortest interval? 10 seconds (freq_subday_type = 2). If you need near-continuous work, use a loop or Service Broker instead.

#Running and stopping

10. Can I run a job from T-SQL? EXEC msdb.dbo.sp_start_job @job_name = N'...'; It returns immediately and does not wait for the job to finish.

11. Can a job start at a specific step? sp_start_job @step_name = N'...'.

12. How do I stop a running job? EXEC msdb.dbo.sp_stop_job @job_name = N'...'; The current step is interrupted, which may leave work half done.

13. What happens to jobs during an AG failover? Jobs are not replicated. Create them on every replica and add a check at the start of each job so it runs only on the primary (sys.fn_hadr_is_primary_replica).

#History and output

14. Why is history missing? By default Agent keeps 1,000 rows in total and 100 per job. Busy servers purge it in days. Raise the limits or archive the history.

15. Where is the full error text? History truncates long messages. Configure an output file or "Log to table" on the step.

16. What does run_status mean? 0 = failed, 1 = succeeded, 2 = retry, 3 = cancelled, 4 = in progress.

#Notifications and security

17. Why are job emails not sent? Database Mail works but Agent isn't using it. Enable the mail profile in Agent properties → Alert System, then restart Agent.

18. Can non-admins manage jobs? Yes, with the msdb roles SQLAgentUserRole, SQLAgentReaderRole and SQLAgentOperatorRole. Each gives a different level of access.

19. How do I see what changed in a job? sysjobs.date_modified and version_number show that it changed. To see who changed it, set up auditing in advance.

20. Do jobs move with a backup/restore of a user database? No. Jobs live in msdb. Script them out, or use dbatools Copy-DbaAgentJob, when you migrate.

Tip

Most "mystery" Agent problems are answered by three queries: job/schedule enabled flags, sysjobactivity for the last run, and sysjobhistory for step messages.