Sunday, August 30, 2020

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



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.


No comments:

Post a Comment

Big Data & SQL

Hi Everybody, Please do visit my new blog that has much more information about Big Data and SQL. The site covers big data and almost all the...