Checking the SQL Server Agent Status

SQL Server Agent is a Windows service that executes the scheduled administrative jobs such as index maintenance, update statistics, database integrity checks, in addition to the application specific jobs. The SQL Server Agent service must be running in order for these job to executed on their scheduled time. What if you are working on an application upgrade that requires you to stop the SQL Server Agent from running and then restart it once the upgrade is completed? What are the commands to stop the SQL Server Agent and start it? Is there any script to check if the SQL Server Agent is running? In this blog, we will explore and share with you on how can we quick stop, start, and run a script to check the SQL Server Agent status.

You can start and stop the SQL Server Agent by using the SQL Server Configuration Manager or the Command Prompt Window using the net commands. We will explore both options here.

Using SQL Server Configuration Manager to start and stop SQL Server Agent:

Open the SQL Server Configuration Manager by running the corresponding Microsoft Saved Console (msc) file for the version of SQL Server you are running:

• SQL Server 2005: SQLServerManager.msc
• SQL Server 2008 and 2008 R2: SQLServerManager10.msc
• SQL Server 2012: SQLServerManager11.msc
• SQL Server 2014: SQLServerManager12.msc
• SQL Server 2016: SQLServerManager13.msc

For example, I am running SQL Server 2012 on our demo server, I will run the “SQLServerManager11.msc” file in the “Search programs and files” textbox:

The SQL Server Configuration Manager will open:

You can then stop, restart, or start any of the SQL Server services installed on the Windows Server.

Using Command Prompt Window to start and stop SQL Server Agent:

To stop the SQL Server Agent service from the command prompt, run the command prompt as the administrator and then key in the following command:

NET STOP SQLSERVERAGENT

To start the SQL Server Agent service from the command prompt, run the command prompt as the administrator and then key in the following command:

NET START SQLSERVERAGENT

NOTE: For named instances, add the $ symbol as in NET STOP SQLSERVERAGENT$NAMEDINSTANCE and NET START SQLSERVERAGENT$NAMEDINSTANCE.

As a SQL Server DBA, you work with multiple SQL Server instances, what if you wish to run a query to check if the SQL Server Agent is running? You can use the following query to check the SQL Server Agent status:

-- Checking the SQL Server Agent Status
-- Copyright © 2016 by JP Chen of DatAvail Corporation
-- This script is free for non-commercial purposes with no warranties.

IF EXISTS(
SELECT 1
FROM MASTER.dbo.sysprocesses
WHERE program_name = N'SQLAgent - Generic Refresher')
BEGIN
SELECT SERVERPROPERTY('ServerName') AS 'InstanceName', 'Running' AS 'SQLServerAgent Status'
END
ELSE
BEGIN
SELECT SERVERPROPERTY('ServerName') AS 'InstanceName', 'Stopped' AS 'SQLServerAgent Status'
END

Script: SQLServerAgentServiceStatus.sql

In the following section, we will stop the SQL Server Agent service, run the query to check its status, start it, and then run the query to check its status again.

Stop the SQL Server Agent Service using the NET STOP SQLSERVERAGENT command.

Run the “SQLServerAgentServiceStatus.sql” script to check the SQL Server Agent service status. It will show “Stopped”.

Start the SQL Server Agent Service using the NET START SQLSERVERAGENT command.

Run the “SQLServerAgentServiceStatus.sql” script to check the SQL Server Agent service status. It will show “Running”.

If you wish to check the SQL Server Agent status for multiple instances, you can register all the required instances and then run a multiple server query. For example:

Datavail Script: Terms & Conditions

By using this Oracle upgrade software script (“Script”), you are agreeing to the following terms and condition, as a legally enforceable contract, with Datavail Corporation (“Datavail”). If you do not agree with these terms, do not download or otherwise use the Script. You (which includes any entity whom you represent or for whom you use the Script) and Datavail agree as follows:

1. CONSIDERATION. As you are aware, you did not pay a fee to Datavail for the license to the Script. Consequently, your consideration for use of the Script is your agreement to these terms, including the various waivers, releases and limitations of your rights and Datavail’s liabilities, as setforth herein.

2. LICENSE. Subject to the terms herein, the Script is provided to you as a non-exclusive, revocable license to use internally and not to transfer, sub-license, copy, or create derivative works from the Script, not to use the Script in a service bureau and not to disclose the Script to any third parties. No title or other ownership of the Script (or intellectual property rights therein) is assigned to you.

3. USE AT YOUR OWN RISK; DISCLAIMER OF WARRANTIES. You agree that your use of the Script and any impacts on your software, databases, systems, networks or other property or services are solely and exclusively at your own risk. Datavail does not make any warranties, and hereby expressly disclaims any and all warranties, implied or express, including without limitation, the following: (1) performance of or results from the Script, (2) compatibility with any other software or hardware, (3) non-infringement or violation of third party’s intellectual property or other property rights, (4) fitness for a particular purpose, or (5) merchantability.

4. LIMITATION ON LIABILITY; RELEASE. DATAVAIL SHALL HAVE NO, AND YOU WAIVE ANY, LIABILITY OR DAMAGES UNDER THIS AGREEMENT.

You hereby release Datavail from any claims, causes of action, losses, damages, costs and expenses resulting from your downloading or other use of the Script.

5. AGREEMENT. These terms and conditions constitute your complete and exclusive legal agreement between you and Datavail.