SQL Server Reference Guide

I've introduced the SQL Server Agent in a previous tutorial, so if you're new to the topic make sure you check out the article called Automating Tasks with SQL Server Agent in the Administration section of this guide. We'll start this tutorial with that information in mind.

The task we are going to automate is very simple, but along the way I'll use it to demonstrate some of the more important features of the SQL Server Agent. You may monitor your backups using other standard methods, or perhaps you have a dedicated tool that you use for backups, and it has its own feedback mechanism. Even so, this tutorial lays out an outline that you can use for almost any kind of task, not just monitoring.

In the introduction tutorial I mentioned earlier I show tools from both SQL Server 2000 and 2005 for the SQL Server Agent, but to keep from jumping around a great deal throughout this tutorial I'll use only SQL Server 2005, which uses the SQL Server Management Studio graphical tool. Everything I show you in this tutorial is possible in both versions. I'll also include commands at the end of each section, since you should be familiar with those as well.

Before you use a tool, you need to have a goal in mind. Not only do you need a goal, but you need to have an understanding of how a given tool will help you accomplish it. With a goal in mind, I normally break out computer automation tasks into five major steps:

1. Determine the task steps and flow
2. Understand the task security
3. Determine the task repetition and schedule
4. Determine the task reporting and alerting
5. Implement and check the solution

Our goal in this case is simple: we want to check the backup dates of all of the databases on a given server. You can get this information, one database at a time, using the SQL Server Management Studio. Connect to a server and right-click the name of a database. Select Properties from the menu that appears, and you'll see the date the last backup was taken there in the top part of the screen, as shown in this screen:

Figure 264

You can see the same information in SQL Server 2000 using the Enterprise Manager tool.

Determine the task steps and flow

The first step is to understand the flow of the process. To do that, you normally have to follow some manual process that achieves the result you want. In this case, we used a graphical tool that shows the last backup date and time of a single database on a server.

But viewing this data one database at a time isn't really the easiest way to make sure all your databases are backed up. You could peruse the log files for the server, since backup operations are recorded there as well, but once again you have to look through more information than you need to find what you're after.

It turns out that the graphical tools in both SQL Server 2005 and 2000 are getting their information directly from the server engine. This information, and more, is found in the backupset table in the msdb database. Here's a query that shows the entire set of database backups performed on this server. It also shows the databases that haven't been backed up, which is what we're really after:

SELECT a.name
, b.backup_finish_date 
FROM sys.databases a
LEFT OUTER JOIN msdb.dbo.backupset b
  ON a.name = b.database_name
ORDER BY backup_finish_date DESC, a.name
GO

With this information in hand, we can now set about automating that query to show the backups on our server. We want to run a query and create a log of the results, or perhaps send an e-mail with the results of the query to ourselves.

There are only two steps in this process, but often you will want to automate far more steps than that. In any case, there is a flow to the tasks involved. That means that we have a series of steps that we want to follow, but the outcome of one step might determine what happens next. You're probably familiar with a simple flowchart, which has shapes that detail what happens when a step of the process succeeds or fails. While you may not need to break out a pencil and paper and draw the steps, you should at least have them in mind as you create your automation. With just these two steps, there isn't much to diagram, but by using some forethought, you can plan a fairly sophisticated task as you work to the goal. You need to think about this from the outset.

For the purpose of this example, there are two primary steps: run the query, and create either a "pull" notification (log file) or "push" notification (e-mail or pager) of the results.

Understand the task security

I'm normally an advocate of thinking about security from the very outset of a process, but in this case you can't evaluate the security of the entire process every time. Within each SQL Server Agent job there are steps, each of which might require different levels of access. For instance, one step might create a file, and another might connect to a database on another server. Each of these requires different security, and if you string together several jobs to automate a more complex process, each job might need to have a different level of access. It's important to think about that when you create the jobs.

Determine the task repetition and schedule

The next step is to figure out the number of times you want to do the task, and when it should happen. These are not the same thing, since you may only want to perform a task once, but you may want it to run next month. In this step you should think about why you want to automate the task. Not only can you run the task multiple times, but you can run it at an inconvenient time (for you, anyway).

Determine the task reporting and alerting

Automating tasks has a dark side. I've seen DBAs "set it and forget it", which is very dangerous. You should always set an alert or notification to contact you on success, not failure. The reason is that if you tell a system to notify you when a process fails and the notification system fails, you believe that everything is working when in fact it might not be. You have unknowingly introduced two points of risk in the system.

When you set a notification for success, the only time you need to investigate is when you don't receive a notice. Whether that means that the notification system is down or the process has failed, it needs to be investigated.

There are three ways that a job can notify you. You can set up an operator in SQL Server that contains an e-mail address, a pager e-mail address and a NET SEND address. I've covered setting up mail in SQL Server elsewhere on Informit, so I won't go into that here, but be careful of relying on the NET SEND mechanism. The System Administrator may have this service turned off, and if you're away from your desk you won't get the message anyway. Use e-mail or logs instead.

If you do use a logging process (my preference), you should remember to check the logs each day. I create logs for most of my system maintenance, so I'm used to doing that task.

Implement and check the solution

At last we're ready to create a job and automate our solution. We understand the query we need to run, and we've tested that it returns what we expect, which is an important thing to do. There's no reason to automate something that doesn't work manually!

Start by connecting to your server using SQL Server Management Studio. Expand the SQL Server Agent object.

Figure 265

Now right-click the Jobs object and select New Job... from the menu that appears.

Figure 266

In this panel you can name the job, and optionally give it a category. The categories aren't terribly useful, but I'm a DBA, so I have to put things in categories. Actually, categories do let you sort and group the jobs later, so they have some use.

I've also provided a description. This is useful for others who have to support the work you do, so make sure you explain any strangeness you've had to code.

Finally, there is a checkbox here to enable the job. You can uncheck this box to suspend a job that you don't want to erase, but you don't want to run. For now leave this option checked.

Click the Steps item on the left-hand side of the panel to move to the next phase of the job setup. Once inside, click the New... button at the bottom of the panel.

Figure 267

First, name the step. This comes from the first part of the analysis you did earlier. The type of jobs ranges from Transact-SQL statements, as we have here, all the way to ActiveX code. Also notice that you're able to set the name of the step owner here, which deals with the security aspect I mentioned earlier.

I've pasted the query from the script I showed earlier, and then clicked the Parse button to ensure that it works, or at least is syntactically correct.

We're not done with this step. The words in the left-hand part of the screen have changed to General and Advanced. Clicking the word Advanced brings up the following panel.

Figure 268

This is where a lot of the power of this tool lives. You have the ability to determine the flow using the On success and On failure actions you see here. You can set how often the step should retry, so that it forms rudimentary self-healing.

You can also send the output of the step to a file, which actually accomplishes both of our steps for this process in one go. You can also log that information to a table or store it in the history table for this job, which you can see using the views and functions I spoke about in my earlier tutorial.

Click OK on this panel, which sends you back to the main panel for the job where you can add more steps using the same process that I just demonstrated.

The rest of the options on the left-hand side of the job creation panel are very straightforward. Follow the same process as above to create and apply any schedules, alerts or notifications that you want to set up based on the information you gleaned using the outline above. We'll return to this job in a future tutorial and expand on these options further.

An interesting feature in SQL Server 2005 is the ability to apply the job to multiple "targets," or other servers. I'll also explain how to do this in another tutorial.

Once you click OK on the jobs panel, your job is complete. As promised, here is the same thing done in code:

USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N’Database Maintenance’ AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N’Database Maintenance’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N’Check Backups’, 
    @enabled=1, 
    @notify_level_eventlog=0, 
    @notify_level_email=0, 
    @notify_level_netsend=0, 
    @notify_level_page=0, 
    @delete_level=0, 
    @description=N’Checks the backups on the server to ensure that they are all running.’, 
    @category_name=N’Database Maintenance’, 
    @owner_login_name=N’YourLoginNameHere’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Run Backup Jobs Query’, 
    @step_id=1, 
    @cmdexec_success_code=0, 
    @on_success_action=1, 
    @on_success_step_id=0, 
    @on_fail_action=2, 
    @on_fail_step_id=0, 
    @retry_attempts=0, 
    @retry_interval=0, 
    @os_run_priority=0, @subsystem=N’TSQL’, 
    @command=N’SELECT a.name
, b.backup_finish_date 
FROM sys.databases a
LEFT OUTER JOIN msdb.dbo.backupset b
  ON a.name = b.database_name
ORDER BY backup_finish_date DESC, a.name
GO’, 
    @database_name=N’master’, 
    @output_file_name=N’c:\temp\test,log’, 
    @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N’(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

After you create any job, you should run it to make sure it works. Just right-click the job and select Start Job at Step... from the menu that appears. If your job has more than one step (this one doesn't), then you can select where you want to start the job.

If you run this job as I have it set up in this example and the server is not the workstation you created the job on, keep in mind that you'll need a C:\TEMP directory and that the output will be on the server, not the workstation.

As you have seen, SQL Server Agent Jobs are very easy to create, and allow you a lot of flexibility. We'll return to them in future tutorials, building on what we've done here and delving further into schedules and notifications.

Informit Articles and Sample Chapters

Still using SQL Server version 7? We haven't left you out. Most of the info in this article still holds, with a few differences. You can read here for more.

Online Resources

The main site to start with is Books Online, Online. Microsoft has a series of articles starting here on the SQL Server Agent.