Optimizing SQL Server Agent

SQL Server Agent is the primary method of executing jobs, alerts, and scheduling in SQL Server. It is also used by replication to coordinate replication events.

Starting SQL Server Agent is simple, but configuring it for optimal use can be a little more difficult. Most administrators breeze right by the advanced Agent properties. To look at the available options, right-click SQL Server Agent under the Management group and select Properties.

General Tab Options

In the General tab, consider starting the SQLServerAgent service with the same account that you use to start the MSSQLServer service. Keep in mind that the Windows account you use to start these two services must have sysadmin rights to your SQL Server.

In the error log section of the General tab, you can change where the logs that are generated by Agent are deposited. By default, the log name is C:\Program Files\Microsoft SQL Server\MSSQL\LOG\SQLAGENT.OUT. Each time you stop and start the SQLServerAgent service, a new log is created and the old log is rolled into a backup copy. Ten copies are kept at any given time. You can also select the Include Execution Trace Messages option to include more detailed trace information when you start Agent.

Caution 

Only select the Include Execution Trace Messages option when you're debugging a problem with SQL Server Agent. If you select this option, your error log can grow quickly and performance may suffer.

To view the error log, click the View button. For quick access to the error log, right-click SQL Server Agent in Enterprise Manager, and select Display Error Log. If you specify a Net Send recipient, Agent will send a broadcast message to the appropriate workstation when an Agent error occurs.

SQL Server Agent uses its own mailer to send messages to operators when an alert is triggered or a job completes. SQLAgentMail configures just like SQL Mail, and has the same requirements. To configure SQLAgentMail, go to the SQL Server Agent Properties screen in the General tab, and select a profile from the Mail Profile drop-down box. Again, the same rules apply to SQLAgentMail as applied to SQL Mail. Before you configure the SQLAgentMail, you must ensure that the account that starts the SQLServerAgent service has the profile configured. This is why it's nice to have the account that starts the MSSQLServer service start SQLServerAgent too.

One of the handy options available in the SQL Server Agent Properties dialog box is the Forward Events To A Different Server option. This allows you to become more efficient as specified errors are written to the local server, and to the application event log of a remote server. With this option enabled, you can receive a consolidated error view for all the servers.

Advanced Tab Options

In the Advanced tab, you can also set the Idle CPU Condition option, which allows you to start a job when the CPU is idle. If you don't set this option, the following warning appears in the SQL Server Agent error log:

[396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect

Alert System Tab Options

Under the Alert System tab, you can set some defaults for your operators (I'll discuss these in the next section). If you set the prefix or suffix option, the setting is placed before and after the pager names. Provide the prefix needed to send mail through a gateway. A suffix can be information like @SQLServerCentral.com. If you set these, you only have to provide the e-mail name; SQL Server appends the specified information to the beginning and end of the e-mail name.

Tip 

Generally speaking, it's better to set the prefix and suffix options at the operator level, not the system level, because your operators may have different e-mail suffixes.

Also on the Alert System tab, you can specify text to appear in your alert message. If you take advantage of this option, you can define the subject line of the message sent to the operator with <Entered Subject> and <Alert Message>. The <Alert Message> is the name of the alert that is triggered-you define it when you create the alert. For example, you might end up with 'Alert message from production server:' (the alert subject), and 'Full msdb Log' (the alert message).

The last option in the Alert System tab is the Fail-safe Operator drop-down box. When this is defined, this operator is paged when an alert occurs and no other operator is available. For example, if you have an operator available between 8:00 A.M. and 5:00 P.M., and your night shift is 7:00 P.M. to 6:00 A.M., this operator is paged if an alert occurs at 6:00 P.M.

Job System Tab

The Job System tab is used for Agent jobs, which are covered later in the 'Jobs' section. By default, the Limit Size of Job History Log is checked. This is to prevent your msdb database from filling up as the job execution log grows. The default settings allow 100 rows for each job and 1,000 for the entire server. For most servers, this would be an ample amount of space, but if you have a server that uses the Agent job system, consider increasing this number. With the defaults enforced, you could only guarantee that 10 jobs (1,000 total/100 rows per job) would be fully logged on your server.

The most important option you can set in this tab is the Non-SysAdmin Job Step Proxy Account option. By default, this option is selected, which means only users in the sysadmin group can execute operating system commands and ActiveX scripts from within a job. If you deselect this option, you must specify a Windows account that these types of steps will use. I'll discuss this in much greater detail in the 'Jobs' section.

Connection Tab

The Connection tab is the place to specify how SQL Server Agent connects with your local SQL Server instance. If you choose Windows Authentication, the account that starts SQL Server Agent, which must be a part of the sysadmin group, is passed to SQL Server. If you choose SQL Server Authentication, you must select a standard SQL account that is a member of the sysadmin role.

In the Trenches

Keep in mind that if you change the password for the account that you use to connect SQL Server Agent to SQL Server, without changing the option in the Connection tab, Agent will not start. For example, if you try to start SQL Server Agent with an incorrect password, you receive the following error in the SQL Server Agent error log:

 [298] SQLServer Error: 18456, Login failed for user 'sa'. [SQLSTATE 28000] [000] Unable to connect to server '(local)'; SQLServerAgent cannot start