Blog Post

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

,

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.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating