Rotating Header Image

Check SQL Server Agent status on all SQL Servers

Every company I’ve worked at had some type of standard schedule to apply (non critical) Windows patching.  Most (if not all) of the time this ends up requiring a reboot of the whole environment which no matter how good your shutdown/startup documentation is tends to be a worrysome time.

If you haven’t played around with SQL Server 2008’s new Central Management Server please take some time to read this great article by Brent Ozar about it:

SQL Server 2008’s new Central Management Server – Brent Ozar

OK so now after reading that article you should be “in the know” about the new CMS.  Some of the groupings I have in my CMS are called DEV, UAT, and PROD.  So if I highlight the PROD group and select “New Query” the first thing I see at the bottom of my Management Studio Window is:

connected

So there is my first “sniff test” that all my SQL Server Instances in my PROD group are alive and well… but what about the SQL Server Agent service… is it running as well?

Copy the code below into your Management Studio query window (UPDATED to include @@SERVERNAME):

IF EXISTS (  SELECT 

           
FROM MASTER.dbo.sysprocesses 

           
WHERE program_name N'SQLAgent - Generic Refresher')

BEGIN

   SELECT 
@@SERVERNAME AS 'InstanceName'AS 'SQLServerAgentRunning'

END

ELSE 

BEGIN

   SELECT @@SERVERNAME AS 'InstanceName'AS 'SQLServerAgentRunning'

END

Your result set should have all the instance names in your PROD CMS Group along with a True/False column as to whether or not the SQL Server Agent Service is up and running.

In utilizing a Central Management Server and running this script you now have a good indication as to whether or not you can connect to all your SQL Servers as well as the status of the SQL Server Agent Service on each of those instances.  Obviously we’re just scratching the surface of the great things you can do with CMS but if you haven’t played around with this new feature this should give you a good place to build from.

Enjoy!!

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

7 Comments

  1. Steve Jones says:

    Might want to add servername to each result set, so you can easily tell which one is down.

  2. hehehe sorry bout that… by default I have the “Add Server Name to the results” set to true for my CMS queries.

    Tools -> Options -> Query Results -> SQL Server -> Multiserver Results

    I’ll change the post 🙂 thanks for the catch.

  3. […] Check SQL Server Agent status on all SQL Servers – Another tip here, courtesy of Colin Stasiuk. […]

  4. […] I wrote a blog post back in 2009 called: Check SQL Server Agent Status on all SQL Servers […]

  5. […] is always NULL so it seems, Microsoft is working on it? more info) #2) By querying it (script by Colin Stasiuk): IF EXISTS (    SELECT 1    FROM master.dbo.sysprocesses […]

  6. Vanessa says:

    Hi Colin,
    Thanks for this article. Is there any way to automate this check through CMS? Ideally, I would like it to check on a regular basis (about every 15-30 minutes) and alert me if it’s down. I just happened to log into one of our test servers today and saw that the agent was down. I have no idea how long it was that way, but I suspect it could have been for a long time. I would like to prevent that from happening in production.
    Thanks!

  7. The Google Pixel may also have an advantage for now over counterparts offered by Android rival Samsung, given the permanent withdrawal from the market of the Galaxy Note 7 after manufacturing defects caused the phone’s batteries to explode

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.