I was taking a practice test for the 70-432 when I got a question that covered a scenario I’d never even thought about before.  It had to do with options you have available on Agent Jobs.  After digging into the Agent Jobs properties dialog, I realized there were some extra features I’ve never used.

While you could find these options in the BOL, I’m going to take the time to walk through the dialog with you, and do a brief overview of the options available, then refer you over to the BOL for more information.

Hopefully this will introduce you to some of these features, and give you an opportunity to take a closer look.

 

General

The first page in the job’s properties allows you to set the name, owner, category, description as well as whether or not the job is enabled.

Normally you’d simply set a name, then move on to the steps page.  But I’d like to point out you can set a category for the job. This can become useful for managing your jobs by category rather than simply by name.

If you’re going to start managing jobs by category, then you might also need to look up how to manage the categories defined on your server.

I’d also like to point out you could also enable or disable a job from the object explorer by simply right clicking on the job name then choosing enable or disable.

One additional note, you can set all these options via T-SQL too!  Start here for further details.

 

Steps

Next, we have the steps the job will take. On this screen we can add, edit, or delete steps as well as re-arrange the order of the steps.

If you want to have finer control over what the steps will do,then you have to add or edit a step.

 

 

Job Step Properties

In this screen you can name your step, choose the type of operation you want to do.

• Transact SQL (T-SQL)
• PowerShell Script
• SSIS Package
• Etc.

When you change the type of step, the dialog changes to allow you to fill in the details for that type of step.  In the image, we’re creating a T-SQL step.  Notice that for T-SQL steps you can identify a user to Run as and the command (either typed directly into the notepad or imported from a .sql file).

Had you chosen an SSIS package, you would choose the package location (either from the SQL Server or a file), as well as the proxy user you’d want to run the package as.  You might want to at least look at the options available for you when using the different package types.  At least look at those I listed above: T-SQL, PowerShell and SSIS.  These are the most common I’ve seen in production.

 

Job Step Properties – Advanced

Now, from the advanced page, we can set what to do if the step succeeds, if it fails, do we want to retry?  If so, how long do we wait before retrying.

Do we want to capture the output to a file?  Do we want to log the output of this step to a file or to a table, perhaps you want to log the output to your job history.

If you choose advanced options for T-SQL, this is the page where you’ll set your Run As User.

Most of the time I spend on this page, I’m setting the what to do for success and failure options.  But the output options are incredibly useful in figuring out what is happening with a failing job.  By default you’ll only get so much information in the Agent log.  With these extra options, you can add more information to your debugging information.

 

Schedules

 

On the Schedules page, you can set up when you want the job to run.  You can create , edit or delete your schedules for the current job.

The one thing I see missed over and over is the “Pick” option.  Rather than having to define what hourly means over and over again, you define it once, and Pick that schedule for all the jobs that need to run hourly.  Even in my current environment there are several jobs with nearly identical schedules that are set up separately. 

The Pick option will let me reduce the number of schedules.  Not only that, but if I want to update the definition of what hourly is, I don’t have to update a dozen jobs.  I update the schedule, and all jobs that use that schedule instantly update to the new schedule.

It’s learning tricks like that which will take you from simply accomplishing tasks, to looking ahead, and making smarter management decisions in the long run.

 

Schedules – Editing

I’m sure you’ve seen this screen if you’ve built any  Agent Jobs.  You can define any schedule you can imagine using this page.

Worst case scenario you’ll have to define two schedules to define a split schedule like I’ve shown here.  I run hourly from 0000 to 0400, then I stop for our maintenance window.  After 0600 I begin again until 0000 the following day.

With Schedules there are always a couple ways to implement your schedule, just keep in mind, you want to reuse your schedules as much as possible.  It saves you effort in the long run.

 

Alerts

The alerts page lets you set up your job to monitor the windows application log looking for a specific event or condition to occur.  When it does occur, you can notify someone that the event or condition has occurred.

From the first screen you can Add, Edit or Delete alerts.

Nothing special here. 

But dig in and create a new alert.

 

Alert Properties – General

Check out the BOL for more details on Alert properties.

You can name you alert here, as well as choose the type of alert you want to wish to create.

• SQL Server events

• SQL Server performance conditions

• WMI events

If you change the type of alert, you’ll get a different set of definitions to define.  I’ll leave it to the BOL to explain the properties.  What I will leave you with here is you can only monitor for a specific event.  If you wanted to raise an alert if any error is raised, then you’d have to define an alert for each error.  That’d be a tremendous pain.  What I would suggest instead is using TRY..CATCH constructs in your T-SQL to capture those errors, and bubble up your alerts through T-SQL instead.

 

Alert Properties – Response

When you define an alert, you choose how to respond to that alert.  You can either start a job, or simply notify someone that it has occurred. 

Most of the time you simply notify yourself that the event has occurred so you can respond to the situation.  But you can set up automated methods to handle these events, if they happen often enough.

From this screen you can also view existing operators, or defined new ones.

That way you don’t have to go over to your Database Mail settings to make those changes.

 

Alert Properties – Options

The last page for Alerts allows you to include error text.  This can be useful in debugging the situation.  If you’re looking for a deadlock error, you might want to throw additional information into the alert that will help you figure out what caused the deadlock.

From this screen you can also send additional text, if you want to beautify the error message any.  You can also set a throttle on how often to fire the response.  If you’re handling an error that happens more than a few times an hour, you may want to use the delay between responses settings. 

Otherwise you’ll drown under the notification load.

 

Notifications

Now, it was an option on this screen that started this whole post.

When you have jobs that are long running and important, you’re going to want to fire off a notification when they complete.  This is the page that will let you do that.

You can send notice via email, pager, net send, or through the Widows Application event log.

You can also send those notifications when the job fails, succeeds, or simply completes.

Now the option I never paid attention to.

Automatically delete the job.  Why is this under notification?  How does it notify anyone of anything by deleting the job? 

While I don’t have a good reason why this option is here, be sure to know it is here, just in case you’re asked to automatically remove a job when it completes successfully.  Otherwise, you’ll end up writing a T-SQL step to delete a job, then scheduling that separately… like I did.

 

Targets

The last page on the Jobs Properties dialog box is Targets.  If you need to run a job against another server, or against multiple servers, this is the page you’ll come to.

To use this page, you’re going to need to look into setting up a multi-server environment.  Then you’ll be able to choose the second radio button, and select the servers you want to run your job against.

 

Summary

This is just a quick study guide that will help you look over the options you have when setting up a job.  If you need more than the overview, I highly suggest either the 70-432 self paced study guide or the BOL.  Either way you’ll get more information.

You need to be familiar with these options before attempting the 70-432, since you will be expected to be able to set up jobs, and use a few of the options that are listed here.  Once you’ve passed the 70-432, you’re definitely going to want to look into the T-SQL equivalents for each of these settings.  After that, move on to the PowerShell equivalents.  That’s where you get some real power settings!

If you have any questions, send them in.  I’m here to help!