Quick Scan Report – SQL Agent Not Running

What this check looks for

The state of the SQL Server Agent service, where the reported state does not begin with Running. Express edition is excluded, because SQL Server Express does not include Agent at all and reporting its absence there would be noise on every Express instance.

The check is skipped on Amazon RDS, where the service is managed for you.

Why it matters

Everything scheduled on this instance has stopped, and the databases carry on looking perfectly healthy while it does.

That is the whole of why this is Critical. SQL Server itself is running. Applications connect, queries return, the instance responds to every kind of monitoring that asks whether it is up. What has stopped is:

  • Backups. This is the one that matters. Every hour Agent is down is an hour added to how much data you would lose, and there is no error anywhere, because a job that does not run does not fail.
  • DBCC CHECKDB, so corruption is no longer being looked for.
  • Index and statistics maintenance.
  • Log backups, which means the transaction log is not being truncated either, so the log is growing on every database in full recovery. That failure arrives later, as a full log drive, and looks unrelated.
  • Log shipping, which stops on both the backup and the copy side.
  • Alerts and operator notifications, including the alerts that would have told you about any of the above.

The last point is what makes this dangerous rather than merely bad. The mechanism that would raise the alarm is the mechanism that has stopped. Nothing is going to tell you. The absence of failure emails reads exactly like everything working.

The usual causes are a service that failed to start after a reboot, a startup type left on Manual, and a service account whose password changed or whose account was locked out.

How to confirm it yourself

EXEC master.dbo.xp_servicecontrol N'QUERYSTATE', N'SQLSERVERAGENT';

On a named instance:

EXEC master.dbo.xp_servicecontrol N'QUERYSTATE', N'SQLAgent$YourInstanceName';

On SQL Server 2008 R2 and later, this also reports the startup type, which is the part worth knowing:

SELECT [servicename],
       [startup_type_desc],
       [status_desc],
       [last_startup_time],
       [service_account],
       [is_clustered]
  FROM sys.dm_server_services WITH (NOLOCK);

Read startup_type_desc as carefully as status_desc. Manual means it will not come back after the next reboot either, and starting it now without changing that leaves the problem in place.

What has not run while it has been down:

SELECT j.[name]                       AS [job_name],
       j.[enabled],
       MAX(msdb.dbo.agent_datetime(h.[run_date], h.[run_time])) AS [last_run]
  FROM msdb.dbo.sysjobs AS j WITH (NOLOCK)
  LEFT JOIN msdb.dbo.sysjobhistory AS h WITH (NOLOCK)
         ON h.[job_id] = j.[job_id] AND h.[step_id] = 0
 GROUP BY j.[name], j.[enabled]
 ORDER BY [last_run];

How to fix it

Start the service, then find out why it stopped, then make sure it comes back on its own. All three, in that order.

  1. Start it. Services console, SQL Server Configuration Manager, or:
net start SQLSERVERAGENT
  1. If it will not start, read the Agent error log, which is separate from the SQL Server error log and lives beside it as SQLAGENT.OUT. The three common reasons:
    • The service account’s password changed, or the account is locked out or disabled.
    • The account has lost Log on as a service.
    • msdb is not accessible, because it is offline, suspect, or in single user mode. Agent cannot start without it.
  2. Set the startup type to Automatic in SQL Server Configuration Manager, and use Configuration Manager rather than the Windows Services console, because it sets the dependent permissions correctly.
  3. Check what was missed. Run the backups by hand for anything whose last backup is now older than your tolerance, and check that log files have not grown while log backups were not happening.
  4. Set up a check that does not depend on Agent. An Agent alert cannot tell you that Agent is down. That has to come from outside: Windows service monitoring, or Database Health Monitor itself, which is what this check is.

How long it takes

About half an hour to start the service and confirm the startup type. Catching up on missed backups takes as long as it takes.

Report Why you would go there
Agent Activity What Agent is doing now that it is running again.
Job History The gap in the history, and what did not run.
Failed Jobs What failed once it started again.
Backup Status Which databases now have an out of date backup.
Missed Runs Scheduled runs that never happened.
Agent Settings The service account and Agent’s own configuration.
Alerts and Operators Whether anything would have told you.
Check
No recent backups The most likely consequence, on its own terms.
Log truncation is blocked Log growth while log backups were not running.
Failed SQL Server Agent jobs What to look at once it is back.
No operators Whether Agent has anyone to tell when it can run again.
Jobs without failure notification The same gap from a different angle.

Frequently asked questions

We use a third party scheduler, not Agent. Then this is expected on this instance, and the thing to confirm is that the third party scheduler is running instead. The check cannot see it.

This is Express. Why is it not reported? Express has no Agent, so the check excludes it deliberately. Scheduling on Express needs Windows Task Scheduler or an external tool.

Agent starts and then stops again. That is almost always msdb. Check that it is online and not in single user or restricted mode, then read SQLAGENT.OUT.

It only runs jobs at night. Can it stay stopped in the day? No. Agent has to be running when the schedule fires. A stopped Agent does not queue work for later, the run is simply missed.