Using Transact-SQL, how can you find out whether SQLAgent is running?
- 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.