How to check if SQL Server Agent is running

In Management Studio, you can check if SQL Server Agent is running by looking at the SQL Server Agent node in Object Explorer. In the following screen shot, SQL Server Agent on my SQL Server 2012 instance is running (green arrow overlaid on the SQL Server Agent icon), but the agent for SQL Server 2000 is stopped (red x).

You can also check in Control Panel > Administrative Tools > Services:

Or in Program Files > Microsoft SQL Server > Configuration Tools > Configuration Manager:

Finally, you can check the state using T-SQL:

DECLARE @agent NVARCHAR(512);SELECT @agent = COALESCE(N'SQLAgent$' + CONVERT(SYSNAME, SERVERPROPERTY('InstanceName')),   N'SQLServerAgent');EXEC master.dbo.xp_servicecontrol 'QueryState', @agent;

If the SQL Server Agent is running, a green play button will be shown in the bottom right corner of the SQL Server Agent icon within SQL Server Management Studio.

To validate the status of the SQL Server Agent for a given instance using T-SQL execute the following code snippet:

IF EXISTS (  SELECT 1              FROM master.dbo.sysprocesses              WHERE program_name = N'SQLAgent - Generic Refresher')BEGIN    SELECT @@SERVERNAME AS 'InstanceName', 1 AS 'SQLServerAgentRunning'ENDELSE BEGIN    SELECT @@SERVERNAME AS 'InstanceName', 0 AS 'SQLServerAgentRunning'END

Source = Colin Stasiuk

The quickest, easiest, most directest way to determine if SQL Agent is running, and that can be done easily in SSMS, is a query (hence it can be automated), and isn't querying a deprecated system table (i.e. sysprocesses) or EXECing xp_servicecontrol, is a DMV that was introduced in SP1 for SQL Server 2008 R2:

sys.dm_server_services

SELECT dss.[status], dss.[status_desc]FROM   sys.dm_server_services dssWHERE  dss.[servicename] LIKE N'SQL Server Agent (%';

Returns:

status  status_desc4       Running

It just requires the VIEW SERVER STATE server permission, but you already needed that in order to see the status of it in Object Explorer (in SSMS).

AND, if you don't want to grant VIEW SERVER STATE to a particular Login because it allows for getting too much other info, then you technically don't need to grant anything at all, at least not to an actual user. See the following two resources for full details (including working examples):

• What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service? (similar question on DBA.StackExchange)
• Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level (blog post)