How Can I Tell if SQL Agent is Running via SQL?

Posted on by David Levy

Introduction

Today’s post is a continuation in my on-going effort to document all of the scripts I use to manage my environment. To date it has been a great exercise both in getting things written down and in getting feedback from others on better ways I could be doing things. To that end, here is a stored procedure that I use to tell if SQL Agent is running before trying to start a job programmatically.

The Script

This script opens with some trickery that I stole from Management Studio using Profiler. This is my new favorite way to determine if a server is a named instance and handle it accordingly. I will be going back to retrofit all of my scripts to use this method.

The next step is to declare a temporary table and pull in the results of xp_servicecontrol to be able to use them later. The xp_servicecontrol extended procedure is undocumented and unsupported but still seems like the best option in this case. Alternatives I have seen include using xp_cmdshell (NO!) or building a CLR stored procedure to call WMI and get this information.

Finally, the service status is selected into the output variable for programmatic use by a caller and the flag is checked to see if the service status should also be returned as a result set for debug reasons.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
CREATE PROCEDURE [dbo].[sp_dba_GetSqlAgentStatus] @display_results bit = 0, @service_state varchar(100) = null OUTPUT

AS

 BEGIN

    DECLARE @agent_string   nvarchar(256)
         
    IF (SERVERPROPERTY('INSTANCENAME') IS NOT NULL)
        SELECT @agent_string = @agent_string + N'SQLAgent$' + CONVERT (sysname, SERVERPROPERTY('INSTANCENAME'))
    ELSE
        SELECT @agent_string = @agent_string + N'SQLServerAgent'

    CREATE TABLE #service_state
    (
        current_service_state   varchar(50)
    )
   
    INSERT INTO #service_state
        EXEC master.dbo.xp_servicecontrol 'QueryState', 'SQLServerAgent', @service_state OUTPUT

    SELECT  @service_state = current_service_state
    FROM    #service_state

    DROP TABLE #service_state

    IF @display_results = 1
        SELECT @service_state AS current_service_state

 END
GO

Conclusion

As usual, I hope you find this script helpful. Please let me know if you run into any issues with it or know a better way to do the same thing. Please keep in mind that scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.

5 thoughts on “How Can I Tell if SQL Agent is Running via SQL?”

  1. Another option:

    1
    2
    3
    4
    IF EXISTS (SELECT 1 FROM sysprocesses WHERE LEFT(program_name, 8) = 'SQLAgent')
      PRINT 'Agent is running!'
    ELSE
      PRINT 'Agent is not connected!';
    Reply

    1. That is a nice simple solution. I think it would serve most people well.

      In my case I would be concerned about relying on string matching. If someone wrote an app to connect using a name like “SQL Agent Check Engine” it would pick up that SQL Agent is running although it just sees itself.

      Another thing to consider would be that there is no error if you do not have the VIEW SERVER STATE permission. The query would just show you that SQL Agent is not running. (http://msdn.microsoft.com/en-us/library/ms179881.aspx)

      Thanks for taking the time to read my post and thanks even more for the feedback! I think my biggest takeaway from this is that I need to make more time for blogging so I can get more into my reasoning behind picking a certain method.

      Reply

  3. First, I want to thank you for putting this together – it helped me with some research that I’d been doing for putting together an automated health check process. I did make some changes however. When setting the @agent_string variable, I took out the part where you first set it equal to itself. When the instance name is a NULL value, that just makes the whole variable equal to NULL. Next, while this ‘seems’ to work ok as-is with the SQL Agent service (although I can’t explain why), after testing this with other services like Full Text, the explicit statement of the service name in the 2nd variable of xp_servicecontrol always refers to the default instance of the installed service. Instead, you will want to replace the second variable in the xp_servicecontrol call with your @agent_string variable to be sure you’re checking the instance in question.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.