T-SQL to determine if SQL Agent service is running.

Question

T-SQL to determine if SQL Agent service is running.

Kenneth.Fisher

SSC-Insane

Points: 20396
More actions
August 17, 2010 at 1:05 pm

#239156

I have written a light weight server monitor that touches each of my servers every morning and checks our backup status, database status etc. I am trying to add a few new features to it including determining if the SQL Server Agent is up and running at the time of the test. I'm using SSIS to make the connections.
Does anyone know T-SQL command that I can use (or SSIS feature) to determine if the Agent is running?
Thanks
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 1

you can use xp_servicecontrol, you can punt the output to a table and then figure if the service is running, you should be able to find a script on here detailing its use.

Andrew

seth delconte SSCertifiable Points: 6388 More actions · Answer 2

andrewkane17 (8/17/2010)
you can use xp_servicecontrol, you can punt the output to a table and then figure if the service is running, you should be able to find a script on here detailing its use.
Andrew

exec master.dbo.xp_servicecontrol 'QUERYSTATE', 'SQLServerAgent'

_________________________________
seth delconte
http://sqlkeys.com

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 3

Thanks guys. I love finding out about those fun little xp/sps 🙂

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]