Making sure SQLSERVERAGENT will be running

Among SQL Server DBAs, you frequently hear horror stories about this-and-that stored procedure suddenly ceasing to function, reliable processes to stop performing as scheduled and so forth.

About 90–plus percent of the time, it’s all being caused by the SQLSERVERAGENT Service having been stopped but not restarted. The SQLSERVERAGENT Service is responsible for all kinds of SQL Server related tasks — if you want to know which tasks that would be on your database server, simply turn it off and wait for the phone to ring. (Just kidding, don’t do that on your production server.)

At any rate, typically when you turn off SQL Server for maintenance or upgrades and the like, you also turn off SQLSERVERAGENT. And although you can take a number of precautions to make sure SQLSERVERAGENT will start automatically, the best approach is to verify that it is running by looking up its current status — either in the Services panel or from within Enterprise Manager.

1) Check up on SQLSERVERAGENT in the Services panel

Control Panel | Administrative Tools | Services

If it doesn’t say “Started” in the Status column, it’s not running. Simply right-click SQLSERVERAGENT and select Start. (Of course, the MSSQLSERVER Service needs to be running first for SQLSERVERAGENT to be able to start, but then again, you already knew that, right?)

2) Check up on SQLSERVERAGENT through Enterprise Manager

Start Enterprise Manager and access the appropriate instance of SQL Server. Then expand the “Management” section and look at the SQL Server Agent node.

If SQL Server Agent does not have a green arrow on its icon, it’s not running. In that case, right-click and select Start. (Of course, this has to be done on the machine on which SQL Server is running; remote connections in Enterprise Manager neither reliably display the SQL Server Agent status nor will they always allow you to start the service.) Ideally, you’ll see something like this:

By the way, you can right-click on the SQL Server Agent node and select Properties to display the corresponding dialog box. On the Advanced tab, you should see a section called “Restart services.” Make sure both options are checked, and you’ll have fewer problems later on when somebody — or something — messes with either SQL Server or SQL Server Agent.

 

Finally…
In addition to manual configurations and double-checking, you can also enable SQLSERVERAGENT to start automatically when the Operating System gets restarted.

Go to Enterprise Manager, expand the instance of SQL Server, then right-click the name of the SQL Server instance and select “Properties” to see something like:

Check all available options in the “Autostart policies…” section, and you’re set.

For good measure, you also want to make sure that the SQLSERVERAGENT Service has been set to a “Startup type” of Automatic, which will prevent a number of problems when it comes to this and that issue having to do with stored procedures, database jobs and scheduled tasks.

If you have any additional insights, tips and information, please let me know. All references to SQL Server in this article refer to SQL Server 2000. Screen captures were taken on Windows XP.