Status of SQL Server Agent

SQL Server Agent is a Microsoft Windows service that executes scheduled jobs and DBAs need to ensure the Agent is running. There are several methods to know the status of the SQL Server Agent.

1) Check if the SQL Server Agent is running in Services.

Control panel –> Windows Administrative Tasks –> Services

Services

2) In SSMS (SQL Server Management Studio), go to Object Explorer, scroll down to find “SQL Server Agent”. Right-click and check the properties.

agentProperties

3) By executing the following query

SELECT [servicename], [status_desc]
FROM   sys.dm_server_services
WHERE  [servicename] LIKE N’%SQL Server Agent%’;

dm_server_services


4) Also, another query to see if there are any processes in “sysprocesses” system table.

SELECT * FROM sysprocesses
WHERE program_name LIKE N’SQLAgent%’

sysprocesses

5) Another query to find out if SQL Server Agent’s status

EXEC master.dbo.xp_servicecontrol ‘QueryState’, ‘SQLServerAgent’;
GO

servicecontrol

6) Open the Microsoft SQL Server Configuration Manager from Configuration Tools and see the status of SQL Agent service. 


Please do let me know if you find any other method.

 

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s