Got Jobs?

I love when I get to demonstrate things I’ve written in the past for you the readers and myself.  I often use my blog as a reference point because I’m just getting way to lazy to Google anything.

So a while back I wrote a post on setting up Registered Servers in Microsoft SQL 2008 R2.  This is what allows you to run commands against a group of server from a single place without needing to manually connect to each one and run your statement.  You can read about them by clicking on either of the two orange links.

This evening I wanted to reboot a couple of SQL servers but I forgot that I had my maintenance plans setup to run and I couldn’t do anything until they completed.  Now in bad practice I do not have alerts telling me my jobs completed.  I manually check them in mornings.  In my defense this will change and I do have them all set to email upon failure. So here I am at 8:30 on Monday night wondering what my job status is.  I could manually log into each server and look at the activity monitor but I want to be lazy dang it!  Then it him that I had setup all my servers as a registered group meaning I could query them all at once to get my answer.  Below is how I did it.

1. I open the SQL server box that has my registered servers setup.  In this instance it’s our General database a catch all if you will. When you see this at the bottom you have two tabs.   One is for the local server and the other is for your registered servers.


2, Click on the tab for Registered Servers and wait a few moments.  You should then be faced with a screen like this. It may take a moment for all the servers to show connected so don’t freak out.


3. Now what I need to is determine what jobs are running against all my servers at this time.  I found the code for viewing job activity on MSDN.  So here we go this is how I run my queries.

The first thing I do is select and expand the group of servers I want to work with.  I then right click on the group container and I’m then given some options to select from.  I choose the option for New Query as highlighted below.


4. With the query window open I tell SQL that I want to run this query against the msdb database on all servers in the group which you can see by the fact the database changed from MASTER to msdb and the number of effected servers are listed as 7 in the results pane.


5. The next thing is to execute my query and see what my results are. BOOM as you can see below  get the information back for all the servers in question.  Now if I wanted to I can filter this out a bit more to reduce the amount of information being returned but this tells me what I need at a quick glance.


So while I won’t say this is the best method to gather this information but in my case it was quick and easy and gave me the information I was looking for.  Below I’ll post the code so you can copy and play with it in your test environment because running code found on the Internet can be bad for your resume… 🙂



USE msdb;
EXEC dbo.sp_help_jobactivity;

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.