Automation Components

There are four basic components for SQL Server Agent, each of which we discuss in the following sections:

• Jobs: Defines the work to be done

• Schedules: Defines when the job will be executed

• Alerts: Allows you to set up an automatic response or notification when an event occurs

• Operators: The people who can be notified regarding job status and alerts

Note

By default, the SQL Server Agent Service is not running, and the service is set to manual after the install of SQL Server. If you are going to be using this in production, be sure to use SQL Server Configuration Manager to set the Start Mode of this service to Automatic.

Jobs

A basic reason to use SQL Agent is to schedule work to be done automatically, like backing up a database. A SQL Agent job contains the definition of work to be done. A job has a name, description, owner, and category. A job can be enabled or disabled. Jobs can be run in several ways:

• By attaching it to one or more schedules

• In response to one or more alerts

• By executing sp_start_job

• Manually via SQL Server Management Studio

A job usually consists of many steps, but it can have as few as one. Each step has a name and a type. Be sure to give your jobs and steps good descriptive names that will be useful when they appear in error and logging messages. The job-step types that you might create are:

• ActiveX Script

• Operating System commands (CmdExec)

• SQL Server Analysis Services Command

• SQL Server Analysis Services Query

• SQL Server SSIS Package Execution

• Transact-SQL Script (T-SQL)

There are other job-step types that you do not usually create yourself. These jobs, with their associated steps, are usually created by setting up replication. The process of setting up replication defines jobs that use these step types, although there is nothing to prevent you from using these step types if you have need to:

• Replication Distributor

• Replication Merge

• Replication Queue Reader

• Replication Snapshot

• Replication Transaction Log Reader

The ActiveX job step allows you to execute VBScript, Jscript, or any other installable scripting language. The CMDExec job step allows you to execute command prompt items. You can execute bat files or any of the commands that would be contained in a bat or cmd file.

The SQL Server Analysis Services Command, SQL Server Analysis Services Query, and SQL Server SSIS Package Execution are new job steps for SQL Server 2005.

The SQL Server Analysis Services Command allows you to execute an XML for Analysis (XMLA) command. This must use the Execute method, which allows you to select data as well as administer and process Analysis Services objects.

The SQL Server Analysis Services Query step allows you to execute a Multidimensional Expression (MDX) against a cube. MDX queries allow you to select data from a cube.

While you could run DTS packages using SQL Agent in SQL Server 2000, you had to come up with a DTSRun CMDExec script or use stored procedures. There was a utility to assist your preparation of the DTSRun command line, but it was an extra step. The SQLServer SSIS Package Execution step allows you to do everything you need to do without going outside the environment. You can assign variable values, configurations, and anything else you need to execute the package. This is a nice improvement and certainly a time saver.

The T-SQL job step allows you to execute TSQL scripts. TSQL scripts do not use SQL Server Agent Proxy accounts described later in this chapter. If you are not a member of the sysadm fixed-server role, the TSQL step will run using your user credentials within the database. When members of the sysadm fixed-server role create T-SQL job steps, they may specify that the job step run under the security context of a specific database user. If they specify a database user, the step executes as the specified user; otherwise, the step will execute under the security context of the SQL Server Agent Service Account.

Note

The GUI for T-SQL security can be confusing. Although there is a Run As: drop-down on the first page of the Job Step Properties page where you set up job steps, this is not the place you set the security for T-SQL steps. The Run As: drop-down here is used to specify security contexts for other types of steps. To set security for your T-SQL step, click the Advanced tab. At the bottom of the dialog is a Run as User drop-down. Set the T-SQL user security context here.

Each job step runs under a security context. The security contexts for other type of job steps are described later in this chapter.

There is some control of flow related to job steps as well. You may specify an action for when the step succeeds and when the step fails. These actions can be "quit the job, indicating success," "quit the job with failure," or "go to another job step." You may also require that the job step be retried before it is failed. You may specify the number of retry attempts and the retry interval in minutes. A job step will be retried the number of times you specify in the Retry Attempts field before it executes the On Failure control of flow. If the Retry Interval in Minutes field has been set, the step will wait for the specified time period before retrying. This can be useful when there are dependencies between jobs. You may have a job step that does a bulk insert from a text file. The text file is placed into the proper directory by some other process, which may run late. You could create a VBScript job step that checks for the presence of the input file. To test for the file every 10 minutes for 30 minutes, you would set the retry attempts to 3 and the Retry Interval to 10.

When you create a job, you can place it into a job category. There are several predefined job categories such as [Uncategorized (Local)] and Database Engine Tuning Advisor. You can also create your own job categories. Each job can be in only one category. You can get to the Manage Categories Dialog Box from the Object Explorer Window of SQL Server Management Studio. Open the SQL Server Agent item in the tree view and right-click Jobs; then select Manage Job Categories. You will get the dialog box shown in Figure 5-1.

Figure 5-1

As trivial as it might seem, give some thought about organizing your jobs. Then create the categories now. You may be surprised how quickly the number of jobs on your server may grow. Sometimes finding the correct job is difficult when there are many.

Job Step Logging

Each time a job is run, job history is created. Job history will tell you when the job started, when it completed, and if it was successful. Each job step may be configured for logging and history as well. All of the logging setup for a job step is on the Advanced Tab of the Job Step Properties. To append the job step history to the job history, select the "Include the step output in history" checkbox.

You may also choose to have the information logged to dbo.sysjobstepslogs in msdb. To log to this table, check the "Log to table" checkbox. To include step history from multiple job runs, also check the "Append output to existing entry in table." Otherwise, you will only have the most recent history.

Job steps executed by sysadm role members may also have the job step history written to a file. Enter the filename in the "Output file" textbox. Also, check the "Append output to existing file" checkbox, if you do not wish to overwrite the file. Job steps executed by others can only log to the dbo.sysjobstepslogs in msdb.

Note

Anytime you refer to network resources such as operating system files, ensure that the appropriate proxy account has the correct permissions. Also, always use the UNC name for files, so the job or its steps are not dependent on directory maps. This is an easy place to get into trouble between the test and production environments if you are not very careful.

In addition to the logging capabilities described so far, output from T-SQL, Analysis Services Steps, and CMDExec steps can be written to a separate output file.

Job Notifications

SQL Server Automation includes the ability for jobs to notify you when they complete, succeed, or fail. In the Job Properties Dialog, choose Notifications, and you will see the dialog box shown in Figure 5-2.

Figure 5-2

A job can send a notification via e-mail, pager, and Net Send.

Note

Windows Messenger Service must be running on the server where SQL Agent is running to send notifications via Net Send. You can send a message to any workstation or user that can be seen from the SQL Agent server.

As Figure 5-2 shows, there is a line in the dialog box for each of the delivery methods. Place a check beside the delivery method you wish; you may choose multiple methods. Each option has a drop-down menu that allows you to choose an operator to notify. An operator allows you to define the e-mail address for the delivery. (Operator setup is described later in this chapter.) Then choose the event that should trigger the notification. It can be when the job completes, when the job fails, or when the job succeeds.

You may not wish to be notified at all for some jobs. However, for mission-critical jobs, you might wish to be e-mailed always when the job completes and perhaps paged and notified through Net Send if the job fails, so you will know immediately.

Schedules

One of the big benefits of SQL Agent is that you can have your jobs scheduled. You can schedule a job to run at any of these times:

• When SQL Agent starts

• Once, at a specified date and time

• On a recurring basis

• When the CPU utilization of your server is idle

Note

You can define when the CPU is idle by setting up the Idle CPU Condition in SQL Agent PropertiesAdvanced. You can define a minimum CPU utilization and a duration. When the CPU utilization is less than your definition for the duration you specify, CPU idle schedules are triggered.

Once your schedule is created, you can associate it with one or more jobs. A job can also have multiple schedules. You may wish to create a schedule for nightly batching and another for end-of-month processing. A single job can be associated with both schedules. If a scheduled job is triggered, and the job is already running, that schedule is simply skipped.

To create a schedule in Management Studio, select SQL Server Agent, right-click Jobs, and choose Manage Schedules. The scheduler is particularly easy to use. You can easily create a schedule that runs on the last weekday of every month. It is very nice not to have to figure out which day is the last day of the month.

The naming of a schedule can be a problem. Should the schedule name have to do with when the schedule runs or what kind of work it includes? We suggest you use both; name regularly recurring jobs "minutely," "hourly," "midnight," and so on. For business-related schedules, you should create a schedule named "Accounts Payable End of Month" or "Bi Weekly Payroll Cycle." The reason for including business names is a matter of convenience. You can disable a schedule; when you do so, that schedule no longer causes jobs to run. If you name many schedules based on business processes and someone instructs you not to run the payroll cycle yet, you can simply disable the associated schedule.

I have never had occasion to create a schedule to run when SQL Agent starts, but there might be some clean up you wish to do. You might wish to be notified when the system restarts; this would be the place to do so.

There are times when CPU idle jobs are worthwhile. If the CPU is not otherwise busy, you can get some batch-related work done. Be careful, however; if you have many jobs scheduled for CPU idle, they will begin to run quickly, and you can overpower your system. Be prudent with the number of jobs of this type that you schedule.

One item that is sorely lacking in SQL Server Agent's arsenal is the ability to link jobs together so that one begins as the other ends. You can make this happen by adding a final step in one job that executes the second job, but that puts all of this navigation inside job steps. It shouldn't be there. It should be outside at the job level. Some third-party tools do a good job of this. However, if you wish to do it on your own, it is likely to be difficult to maintain.

Operators

An operator is a SQL Agent object that contains a friendly name and some contact information. Operators can be notified on completion of SQL Agent jobs and when alerts occur. (Alerts are covered in the next section.) You may wish to notify operators who will fix problems related to jobs and alerts, so they may go about their business of supporting the business. You may also wish to automatically notify management when mission-critical events occur, such as failure of the payroll cycle.

You should define operators before you begin defining alerts. This allows you to choose the operators you wish to notify as you are defining the alert, saving you some time. The Operator Properties Dialog box is shown in Figure 5-3.

Figure 5-3

The operator name must be unique and fewer than 128 characters.

For e-mail notifications, you can provide an e-mail address. You may provide multiple e-mail addresses separated by semicolons. This could also be an e-mail group defined within your e-mail system. If you wish to notify many people, it is better to define an e-mail group in your e-mail system. This will allow you to change the list of people notified without having to change every job. One undocumented capability is that you can specify multiple e-mail addresses for an operator, but they must be separated by semicolons.

For pager notifications, you also provide an e-mail address. SQL Agent does not know anything about paging. You must have purchased paging via e-mail capabilities from a third-party provider. SQL Agent merely sends the e-mail to the pager address. Your pager software does the rest. Some pager systems require additional configuration characters to be sent around the Subject, CC, or To line. This can be set up in SQL Agent Configuration, covered at the end of this chapter.

Notice that there is a Pager on Duty Schedule associated with the Pager E-mail Name. This applies only to pagers. You can set up an on-duty schedule for paging this operator and then set this operator to be notified from an alert or job completion. When the job completes or the alert occurs, the operator will only be paged during his or her pager on-duty schedule.

You can also use Net Send to notify an operator. When Net Send is used, you must provide the name of the workstation for this operator, and a Message Dialog box will pop up on his or her workstation. This is the least-safe way of notifying, because the operator may not be at his or her desk.

Scheduling Notifications

Jobs allow you to notify a single operator for each of the three send types: e-mail, pager, and Net Send. Notifications from alerts allow you to notify multiple operators. This allows you the capability to do some very nice things. You can create an operator for each shift (First Shift Operators, Second Shift Operators, and Third Shift Operators), set up a group e-mail and a group page address for each of the shifts, set up the pager-duty schedule to match each shift's work schedule, and add all three operators to each alert. If an alert set up like this occurs at 2:00 A.M., only the third-shift operators will be paged. If the alert occurs at 10:00 A.M., only the first-shift operators will be paged.

There are several limitations of the schedule. Notice that the weekday schedule must be the same every day, although you can specify a different schedule for Saturday and Sunday. There is a big opportunity here for an enterprising individual. There is nothing to indicate company holidays or vacations. It would be really nice to be able to specify company holidays and let the schedule use the weekend schedule instead of the weekday schedule. It would also be nice to allow operators to specify vacation days that would be integrated into the system. You can disable an operator, perhaps because they are on vacation, but you cannot schedule the disablement in advance. There are lots of opportunities for an add-in. Microsoft has maintained this limitation with essentially no improvements since SQL 7.0.

You can tell if a job, alert, or operator is disabled without having to go into the properties of the object. Management Studio tags disabled objects for SQL Agent with a small red down arrow. In Figure 5-4, one alert and one operator have been disabled.

Figure 5-4

Note

To use e-mail or pager notifications, Database Mail must be set up and enabled, and SQL Agent must be configured to use it. For pager notifications, you must have a third-party pager notification system. To use Net Send, Windows Messaging Service must be running on the same server as SQL Agent.

Failsafe Operator

What happens if an alert occurs and no operator is on duty, according to their pager on-duty schedule? Unless you specify a failsafe operator, no one would be notified. The failsafe operator is a security measure that allows an alert notification (not job notification) to be delivered for pager notifications (not e-mail or Net Send) that could not be sent.

Failures to send pager notifications include:

• None of the specified operators are on duty.

• SQL Server Agent cannot access the appropriate tables in msdb.

Note

SQL Server Books Online says that bad pager e-mail addresses will also cause the failsafe operator to be used, but in testing using SQL 2005 SP1, this was not the case.

The failsafe operator is only used when none of the specified pager notifications could be made or msdb is not available. An example of this is the following: You have a long job that begins. The msdb database then fails for some reason, although SQL Server is still running, as is SQL Agent. The job needs to do a pager notification, but the sysoperators and sysnotifications tables in msdb are not available. SQL Agent will read the registry and obtain the failsafe operator information and send the page.

If you have three pager operators associated with a specific alert, and one of them is notified, but two of them failed, the failsafe operator will not be notified.

You can indicate whether the failsafe operator will be notified using any or all of the three notification methods. Don't get confused here. A failsafe operator will only be notified if a pager notification cannot be successfully delivered. However, the failsafe operator in this case can be notified via e-mail, pager, Net Send, or a combination of these methods. Since the failsafe operator is a security mechanism, you may not delete an operator identified as failsafe. First, you must either disable the failsafe setup for SQL Agent, or choose a different failsafe operator. Then you can delete the operator.

You can disable an operator defined as failsafe. Disabling this operator will prevent any normal alter or job notifications from being sent but will not restrict this operator's failsafe notifications.

Alerts

An alert is a predefined response to an event. An event can be any of the following:

• SQL Server event

• SQL Server performance condition

• WMI event

An alert can be created as a response to any of the events of these types. The responses that can be triggered as the result of an event alert are:

• Start a SQL agent job

• Notify one or more operators

Note

You may only notify one operator for each notification type for job completion, but you may notify a list of operators for alerts.

When you create an alert, you give it a name. Ensure that this name tells you something about what is going on; it will be included in all messages. Names like "Log Full Alert" or "Severity 18 Alert on Production" might be useful.

Alert Event Types

You then choose the event type on which the alert is based, as shown in Figure 5-5. Three event types are covered in this section.

Figure 5-5

SQL Server Event

The SQL Server event alerts are based mainly on error messages. You can create an alert on a specific error message number or on an error severity. You might create an alert on error message number 9002 (log file full) or 1105 (out of disk space) message. An alert can be fired for any particular database or all databases. You may only care to get this alert when the Production database transaction log is full, not when the other test and development databases run out of log space. In this case, choose the Production database in the Database name drop-down list. I wish this dropdown was a multiselect, but it is not. If you wish to alert on two databases, but not all of them, you will have to create two separate alerts.

Note

It is very common to set up 9002 alerts for your important databases to notify you if the database is out of log space. By the time your users call on the phone, you can tell them you are already aware of the problem and are working on it.

You can also choose to create an alert on a specific error-severity level. Each error message has an error-severity level. Severity 19 and above are fatal server errors. You may wish to create an alert for each of the severity levels between 19 and 25, so you can be notified of fatal SQL errors in any database.

What happens if you create an error on a message that has a severity level of 16, then create another alert of all severity 16 messages? When both the message number and message severity are covered in two separate alerts, only the message alert will fire. You can think of the severity-level alert as a backup. Alerts defined on specific message numbers will fire when the message occurs. For all other error messages for that severity, the severity-level alert will fire. This is a nice implementation.

You cannot create two message-level or severity-level alerts with the same message or severity level and the same database. You can create an alert on message number 50001 for AdventureWorks and another alert on message number 50001 for <all databases>. In this case, when the error message occurs in AdventureWorks, the alert for AdventureWorks will fire, not the <all databases> alert. The <all databases> alert will fire for a message number 50001 that occurs in any other database other than AdventureWorks. The lesson here is that the most local handling of an event is the one that fires.

You may also create an alert that has an additional restriction on the text of the message. You can create an alert as before, but check the box "Raise alert when message contains:" and enter a text string in the textbox. The alert will then fire only on messages that include the specified text. You can create an alert on user messages severity (Severity 16) for all databases, which fires when the text 'Page Bob' is included in the error message. Then applications could raise user errors that cause the alert to occur, paging Bob. This is a great way to amuse your friends and annoy Bob. The same principle as before applies: If a message with matching text is sent, the associated alert fires. The more general alert will fire if there is no text match.

SQL Server alerts work by watching the operating system application event log. If the event is not logged, the alert will not fire. The application log might be full, or the error might not be logged. You can create error messages with the sp_addmessage stored procedure. You may specify whether the message is logged or not. For example, you can create a simple message using the following SQL:

 sp_addmessage 50001,16 ,'MESSAGE', @with_log = 'TRUE' 

This message has a message number of 50001 and a severity level of 16. You can then create alerts to test your system. Set these alerts to use e-mail as the response. To test the alert, use the following code:

 Raiserror(50001,16,1)with log Select * from msdb.dbo.sysmail_allitems 

Raiserror sends the error message. Notice the included text with log. This is not necessary, because this message is always logged. You can cause an error message to be logged using the Raiserror command, if you have the appropriate permissions.

The second statement displays all of the mail items. Scroll to the bottom of the list to check for the mail notification that has been attached as a response to the alert.

SQL Server Performance Condition

When you install SQL Server, a collection of Windows Performance Monitor counters are also installed. The Windows Performance Monitor tool allows the operations staff to monitor the performance of the server. You may monitor CPU utilization, memory utilization, and much more. When SQL Server is installed, an additional collection of monitor counters is added to allow DBAs to monitor the performance and status of SQL Server instances. You can create an alert on a condition based on any of these SQL Server counters.

Note

You cannot create SQL Server alerts on counters that are not specifically for SQL Server, like CPU utilization. However, the Performance Monitor tool gives you the capability to set alerts for these other non-SQL Server counters.

A SQL Server Performance Condition alert is shown in Figure 5-6.

Figure 5-6

Performance counters are grouped according to their objects. For instance, the Databases object contains the counters associated with a specific database, like Percent Log Used and Transactions/sec. The Buffer Manager object includes counters specific to buffer management. You choose the object, then the counter for which you wish to create an alert.

Note

You cannot create multicounter alerts. You cannot create an alert that fires when Percent Log Used is greater than 80 and Transactions/sec is greater than 100. You must choose to alert on a single counter.

The next choice you make is in the instance box. When you choose Databases objects, the instance box will contain the list of databases. You select the database on which to create the alert.

Next is the "Alert if counter" box. You can alert if the counter falls below, becomes equal to, or rises above a value you specify. You specify the value in the Value textbox.

You have seen how you can create an alert to notify you when the transaction log becomes full. That is really a little too late, although better than no alerts at all. It would be much better to know when it looks like the log may become full but before it actually does. To do this, create a Performance Condition alert on the Databases object, Percent Log Used counter for the database you are interested in. Choose when the counter rises above some safe limit, probably 80 to 95 percent. You will then be notified before the log is full. You will need to adjust this actual value so that you do not get notified too quickly. If you have set up your log to be what you believe is large enough, you might instead wish to notify on autogrowths.

Alert Responses

As you already know by now, you can respond to an alert by starting a SQL Agent job or notifying one or more operators. You set this up on the Response tab of the create Alert Dialog box. To execute a job, all you must do is check the checkbox and choose an existing job or create a new job. To notify an operator, check the appropriate box, and select the operators you wish to notify by choosing one or more of the notification methods. For alerts, it is really nice to have an operator for each shift you must cover, with the pager on duty set up appropriately, as discussed in the "Operators" section earlier in the chapter.

As you think about how you might best use this in your enterprise, imagine a scenario like the transaction log getting full. You could set up a performance alert to notify operators when the log is actually full and run a job that grows the log. You could set up an alert that backs up the log when it becomes 70-percent full and create another that backs up the log again when it is 80-percent full.

The scenario might play out like this. You are having lunch. Your pager goes off, notifying you that the log is 70-percent full. A job is run automatically that tries to back up the log to free space. In a couple of minutes you get a page telling you that the job completed successfully. After a couple more potato chips, your pager goes off yet again - the log is now 80-percent full. The prior log backup did not free up any space. There must be a long-running transaction. The log backup job is run again, and you are notified on its completion. You finish your lunch with no other pages. This means the last log backup freed up some space and you are now in good shape. Your pager may have gone off again, telling you that the log is completely full and the job that automatically increases the log size has run. It's probably time for you to get back to work, but the automation you have brought to the system has already been fighting this problem while you ate your lunch, notifying you of each step. With some thoughtful consideration, you might be able to account for many planned responses such as this, making your life easier and operations tighter.

The Alert Options page in the Create Alert dialog box allows you to do several things:

• Specify when to include more detailed information in the notification

• Add information to the notification

• Delay the time between responses

Sometimes the error text of the message might be long. Additionally, you may have a limit on the amount of data that can be presented on your devices. Some pagers limit you to as few as 32 characters. You should not include the error text for those message types that cannot handle the extra text, which are most commonly pagers.

There is a large textbox labeled "Additional notification message to send." You can type any text here, and it will be included in the notification message. Perhaps something like "Get up, come in, and fix this problem immediately" might be appropriate. Perhaps "Remember when you come in on the weekend or at night to fix a problem, you receive a $300 bonus, so don't feel bad" might even be better.

At the bottom of the page, you can set a delay between responses. The default value for this is 0. Imagine a scenario where an alert goes off many times during a very short period. Perhaps a program is executing raiserror over and over or a performance condition alert is going wild. The performance condition alerts that run because of limited resources are especially vulnerable to this problem. You run low on memory, which causes an alert or job to run, which uses memory. This causes the alert to fire again, using more memory, over and over. You get paged over and over as well. Knowing that the transaction log is getting full 20 times a second is no more useful than hearing about it every five minutes. To remedy this problem, set the delay you wish in minutes and seconds. When the alert occurs, it will be disabled for your delay time. Then it will be automatically enabled again. If the condition occurs again, the process starts all over again. This is a setting that you might find very useful from time to time.

You can right click any of the SQL Agent objects and create a script that can drop or create the object. If you wish the same object to exist on many servers, you can script it out, change the server name, and load it onto a different server. This would mean you would have to keep operators, jobs, alerts, and proxies in sync between multiple servers, which could be painful and error prone. We cover how to do multiserver jobs in a later section titled "Multiserver Administration."

Event forwarding can also simplify your life when you administer many servers. Event forwarding is covered in the "Advanced" section later in the chapter.