How to verify if SQL Server Agent is running?

I read a blog post last week with the title of my subject line. The first thing I thought was wow. You have to check manually? There are no service state alerts in place? But then thinking back, I can remember a few places without formal monitoring solutions in place. In one case, during my 1st week at a new gig, I learned it only after someone else let me know that a service was down. Not good.

There are numerous apps available for monitoring SQL Server, or other Windows services. I've worked with Nagios, Zabbix and several others, but I've also always tried to have something set up on the SQL side of things. That's all this post is. Just a quick script you can use to check the state of the SQL Server Agent, and alert if it is not running.

         SET NOCOUNT ON;

         /*  

           First we load the service name and state into a local variable. 

           Then we check the status, and email and alert if service state <> 'Running'.    */

         -- local table variable to hold our results

         DECLARE @Services TABLE (

        ServerName VARCHAR(50),

       ServiceName VARCHAR(50),

           ServiceState VARCHAR(10)

)

        -- check the SQL Server Agent service

        INSERT @Services (ServiceState)  

        EXEC master..xp_servicecontrol 'QueryState', 'SQLSERVERAGENT'

        UPDATE @Services 

        SET ServerName = @@SERVERNAME,

        ServiceName = 'SQLServerAgent'

  

        -- Check for status <> Running, send email if exists

        IF EXISTS(

        SELECT 1 FROM @Services

        WHERE ServiceState <> 'Running.')

        BEGIN

                EXEC msdb..sp_send_dbmail 

                      @profile_name = 'YourDatabaseMailProfile',

                      @recipients = 'YourDBATeam@domain.com',

                      @subject = 'SQLServerAgent_Service_Alert',

                      @body = 'The SQL Server Agent service is not running. Please review.',

                      @importance = 'High'

        END

        ELSE

                RETURN;

        SET NOCOUNT OFF;

That is just one example targeting the SQL Server Agent. You can very easily change it to check all of SQL Server's services, alerting if any of them are found not to be running. Here's a quick read for you on the other service names, such as the Browser, SSIS or the SQL Server itself.  

http://blogs.technet.com/b/fort_sql/archive/2010/05/31/list-of-sql-server-service-names.aspx

Oh yes, you also need to remember, xp_servicecontrol is an undocumented extended stored procedure. Therefore, it is unsupported by MSFT. It could be pulled from the next release, or changed at anytime... without any type of notification. You need to be very wary about using anything like this in production.