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
2) In SSMS (SQL Server Management Studio), go to Object Explorer, scroll down to find “SQL Server Agent”. Right-click and check the properties.
3) By executing the following query
SELECT [servicename], [status_desc]
FROM sys.dm_server_services
WHERE [servicename] LIKE N’%SQL Server Agent%’;
4) Also, another query to see if there are any processes in “sysprocesses” system table.
SELECT * FROM sysprocesses
WHERE program_name LIKE N’SQLAgent%’
5) Another query to find out if SQL Server Agent’s status
EXEC master.dbo.xp_servicecontrol ‘QueryState’, ‘SQLServerAgent’;
GO
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