:::: MENU ::::

SQLAgent status through T-SQL

  • Jan 07 / 2010
  • 0
DBA Interview questions, dbDigger, SQL Server Agent scheduled Jobs, T-SQL Interview Questions

SQLAgent status through T-SQL

Question:

Using Transact-SQL, how can you find out whether SQLAgent is running?

Answer:

  • sysprocesses table for program_name like SQLAgent%
  • Use xp_servicecontrol querystate SQLSERVERAGENT

Explanation: Querying the sysprocesses table will return a row if sql agent is running, else it will not return any rows. The xp_servicecontrol procedure will return the status of the service (running/stopped) on 2005 / 2008 editions except express. On a SQL Server Express instance this command will return an error. So you need to check for errors as well as the status to determine whether the agent is running.

Note: The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.

Consult us to explore the Databases. Contact us