Visualizing Jobs (and more!) in Event Manager

I was reading a recent post by Thomas Rushton (b/t) on importing SQL Server Agent history in order to view them on Outlook's calendar. I remembered back to the days when I was a production DBA and was trying to find the best way to (a) track all of those jobs and (b) ensure that they weren't running concurrently. SQL Server Management Studio doesn't provide an easy way to see those schedules or alert you when jobs are starting to take longer. I had built tables that would record the average run time, but a lot of manual effort was put into reviewing those logs and making sure that jobs didn't end up running on top of each other.

Obviously I would be remiss if I didn't take this opportunity to talk about SQL Sentry Event Manager. To emphasize how important and difficult it is to manage job schedules, Event Manager was the first tool released by SQL Sentry, way back in May, 2004. The purpose of Event Manager is the ability to see all of the events that are occurring across your environment - not just jobs - in an "Outlook-style" calendar view.

Figure 1 : Event Manager Calendar

Figure 2 : Custom Event Views

Figure 3 : Select Instances

Figure 4 : Select Event Sources

Figure 5 : Set Filters

Figure 6 : Change Properties

In addition to jobs, this can include Top SQL events, SQL Agent Alerts, blocking, deadlocking, Custom Conditions, and several more, across any number of servers. Being able to look at concurrent and overlapping events is relatively impossible within SQL Server Management Studio for just one server, never mind multiple; SSMS also can't visualize future events in any meaningful way, while Event Manager does this with ease.

You can also filter the events that you can see on the calendar. I filter out several things that amount to noise; for example, any events that succeeded and ran for less than one minute. I'm a busy DBA, so I don't need to see those any more than I need to review all the successful log backup messages in the error log. You are also able to filter the types of events that you're seeing, the connections that you're seeing events for, set the time period that you're seeing, look for specific objects, and other filters. Event Manager is not the tool that gets the most attention here at SQL Sentry, but I've always found it to be extremely helpful, and I'll show you one way it can help you here.

As I mentioned earlier, you can dynamically filter what shows on the Event Manager calendar. But what if this is something that you want to see on a regular basis? We have a piece of functionality called Custom Event Views. This allows you to create a filtered view so that you can access it as needed. Here I'll go through the steps for creating a Custom Event View for jobs that perform full database backups.

You'll first want to find Custom Event Views on the Navigator Pane (see Figure 2, at right). There are two types of Custom Views: Local and Shared. Local views are only available to you, while shared views are available to anyone in your organization who has access to the client. You'll want to right-click on either Local or Shared and select New to create a new view.

In the Add A New Custom Event View window, you'll want to provide a name for the view and an optional description. Now let's start building the view.

First, we'll want to decide which servers we want to include. The Select Instances window (see Figure 3, at right) allows you to choose individual instances or all instances of a certain type. The other option on this window is to filter for keywords. You can enter multiple keywords separated by semi-colons, and you can exclude words by prefacing them with a hyphen. This view is for full backups, so I'm going to look for the word backup and exclude any transaction log backups, so my filter on Event Object Name is Backup; -tlog.

While you can choose individual events (like specific jobs), I know that I only want to look for SQL Server Agent Jobs that have my keywords in the name. So on the Select Event Sources dialog, I'll uncheck everything but SQL Server Agent Jobs (see Figure 4, at right).

The final step in this window is to set filters. There are a number of options here, but for this view, I want to see future events, so I check the 'Future' option on the Set Filters dialog (see Figure 5, at right). After completing this, I save the view, and it will show up on my list of Custom Event Views. If I double-click on that view, I will see all of my full backup jobs in an Outlook-style calendar.

There's one final option that I can make to this Custom View. If I have the view open, I can click on Event View (on the right side of the screen) and choose Properties. In the properties panel, I can set how many days I would like to view, the default interval, and if I want the view to start at a specific time (see Figure 6, bottom right). I find this is very helpful; for example, if all of your backups start after 10:00 PM, you can set the specific time to start at 10:00 PM.

In addition to the ability to see all of our backup jobs in a single calendar view, there is still more functionality to explore. In this specific case, I can see that there are a couple of backup jobs that are overlapping at around 3:00 PM, as shown in Figure 7 (click to enlarge):

Figure 7 : Overlapping Jobs

Instead of jumping into Management Studio to change the schedule, I can click and drag any of those jobs within Event Manager to reschedule. A dialog box will open, ensuring that this is the change that you want to make (see Figure 8). Clicking OK will reschedule that instance of the job and that change will be reflected in the SQL Server Agent Job schedule.

Figure 8 : Change Schedule

Now, it would be even nicer if these backup jobs dynamically re-scheduled future events when previous backups have taken longer. Of course, SQL Sentry has a way to do that by using Event Chains, which I think might be my next blog post topic.