Configuring SQL Server Agent

Now that you have learned how things work in SQL Agent, you can take on the configuration task. You already know about many of the configurables, so now we will simply go through the dialogs with a brief description.

To start configuration, right-click SQL Agent node with Management Studio and choose properties. You will be shown the General page, as shown in Figure 5-10.

Figure 5-10

You should make sure to check the two top checkboxes: "Auto restart SQL Server if it stops unexpectedly" and "Auto restart SQL Server Agent if it stops unexpectedly." Processes will watch both of these services and bring them back up if possible.

We usually leave the error-log location to the default, but you can change it if you wish. If you need some additional logging, you can check "Include execution trace messages."

To get a Net Send when errors are logged, enter a workstation name in the Net Send recipient textbox. Of course, Windows Messaging Service must be running on the server for Net Sends to occur.

Now choose the Advanced Page on the top left, and you see the dialog box shown in Figure 5-11.

Figure 5-11

The top section, SQL Server event forwarding, allows you to forward your events from one server to another. You can set up operators and alerts on a single server, then have the other servers forward their events to the single server. If you plan on using this capability, you also need to understand how to use SQL Agent tokens, which are covered in the "Using Token Replacement" section.

If you wish this server to forward its events, check the box labeled "Forward events to a different server." Then select the server name. You can forward all events or only unhandled events. An unhandled event is one that does not have an alert defined for it. You also select how severe the error must be before it will be forwarded. You may not wish anything less than a severity 16 (Miscellaneous User Error) to be forwarded. Whether or not you forward 16s depends on whether you have applicationdefined errors that expect to do notifications.

The second section is "Idle CPU condition." Recall that you can create a schedule that runs when the CPU becomes idle. This is the place where you define what idle means. The default is CPU utilization at less than 10 percent for 10 minutes.

The next page is for the Alert System, as shown in Figure 5-12.

Figure 5-12

If you plan to use Database Mail or the older SQL Mail, you do the setup here. Although you may have many mail profiles in Database Mail, SQL Agent will use only one profile. Choose the mail system and profile.

The second section is for pager e-mails. If your pager system requires special control characters in the To:, CC:, or Subject line, you may add those characters here. You may add these control characters in front of the item (prefix) or after the item (suffix). As you make changes, you can see the effect in the small box below your data-entry section. You may also choose to include or exclude the body of the e-mail for pagers by indicating your selection in the appropriate checkbox.

The third section allows you to provide failsafe operator information. Please use this if you are doing any notifications. It is too easy to change a schedule in such a way that results in no one getting notified, so don't get caught. Enable this section, choose an operator, and indicate how the failsafe messages should be delivered (by e-mail, pager, Net Send, or some combination of these).

The last checkbox allows you to specify whether or not you wish to have tokens replaced in jobs run from alerts. Details of token replacement are covered in the Advanced section.

The Job System page is next, as shown in Figure 5-13.

Figure 5-13

You are trying to shut down SQL Agent, and jobs are running. How long should SQL Agent wait for jobs to complete before killing them and shutting down? You specify that period in the Shutdown time-out interval (in seconds) list.

The second section is only available if you are administering a SQL Server 2000 Agent. This allows you to set the backward-compatible nonadministrator proxy. SQL 2000 only allowed one proxy. SQL 2005 allows many, so this is not necessary when administering SQL Server 2005 Agents.

The Connection Page is one that most users will not need. SQL Agent connects to SQL Server, by default, using the server name, default port, the SQL Agent Service account, and the highest-matching protocol between the client configuration and the protocols enabled for SQL Server. There are several circumstances where you may wish to alter these defaults:

• Your server has multiple network cards and you wish to specify a particular IP or port.

• You wish to connect using a specific protocol (IP, for instance).

• You wish SQL Agent to connect to the server using a login different from the service account login.

To accomplish this, you create an alias for the SQL Server, using Configuration Manager. Expand the SQL Native Client Configuration, right-click Aliases, and choose New Alias. Then set up the alias to suit your connectivity needs. Then on the SQL Agent connection page, enter the alias name and the connection information you wish for SQL Agent to use. Although SQL Server authentication is allowed, it is not recommended.

The last page is the History Page. You need to think about these settings. You can limit the size of the job-history log to a fixed number of rows. That's easy and fine to do. The Maximum job history rows per job is really a life saver, however. Imagine a job that runs over and over. It could be a job scheduled by a user to run every second, or it could be a job that runs from an alert that occurs over and over. In any case, the log entries from this job could fill up your entire job history, and you would have no history information for any other jobs. That could leave you in a tough spot if any other job needed debugging. This is exactly the situation that Maximum job history rows per job is intended to prevent. The default is 100 rows, but you can change it based on your needs.

New to SQL 2005 is the ability to remove older history rows, even if you have not reached your upper limit. Simply check the box, and set the age.