T-SQL Tuesday: Default Settings for SQL Server Agent

This month's T-SQL Tuesday topic, hosted by Andy Yun (b|t) is "Just Say No to Defaults". Thanks Andy for hosting this month and picking a topic with so many possibilities!

Where to Begin?

There are a lot of default settings in SQL Server that I, along with most experienced DBAs, routinely change via scripts I have stolen developed. Most are applied at the server level, such as:

• power management settings
• sp_configure 'show advanced options'
• max degree of parallelism
• cost threshold for parallelism
• backup compression default
• backup checksum default
• optimize for ad hoc workloads
• max server memory
• remote admin connections
• tempdb configuration
• instant file initialization

Others relate to configuration of individual databases:

• data and tlog file location
• autogrowth and maxsize settings
• page verification
• parameterization
• … and many more!

SQL Server Agent

Today, however, I want to discuss a completely different SQL Server component - SQL Server Agent. This critical component for most production SQL Server installations is disabled by default. Making it a useful and trustworthy part of your SQL Server administration infrastructure is often a time-consuming, unending process. There are no wizards for the inexperienced, accidental or occasional DBA, and the official documentation, while reasonably thorough in explaining the essential parts and pieces, does not provide a clear guide to exploiting SQL Server Agent's capabilities, never mind dealing with its limitations.

With all that said, here is my list of SQL Server Agent properties, features, and related components that I configure on a production SQL Server instance:

• Forward events to a different server: many years ago when I was managing a fairly large SQL Server environment (and before the advent of SQL Sentry Event Manager) I enabled and configured this option as a way to provide some level of centralized event management. I find less of a need for it these days.

• Define idle CPU condition: essential if you want to schedule a job to run during idle CPU time.

• History:

  Current job history log size - these settings (max job history log size (in rows) and max job history rows per job) control the number of rows stored in msdb.dbo.sysjobhistory. The defaults are rarely an appropriate balance between maintaining enough history for effective Agent job troubleshooting and limiting the size of the msdb database.

  Remove Agent History - specifies a cap on how long job history is retained. This option is disabled by default. I usually enable this and set a retention period beyond when I normally need to research the status of job executions.

  These settings usually require tweaking based on the number of SQL Agent jobs and the frequency of job execution. An environment with widely varying job schedules may require code that manages the retention of job and job step history on a per-job basis.

• SQL Agent Alerts: Microsoft stopped including default ("demo") alerts as part of SQL Server installations somewhere around SQL Server 2005. So if you want to leverage this powerful feature, you need to create your own alerts, or download a script that will create a starter set for you - such as the one provided by Glenn Berry here. To learn more, check out SQL Agent Alerts and SQL Server Alerts: Soup to Nuts.

• Operator(s): Before you can use SQL Agent as an alert system to send notifications you have to define one or more individuals or groups to receive them. Operators can receive notifications from alerts and jobs. You can also define a fail-safe operator who will be notified under the following conditions:
  • SQL Server Agent cannot access the msdb system tables
  • All pager notifications have failed
  • The designated operator for an alert or job is off-duty, as defined in their pager schedule)

• Database Mail is SQL Server's solution for sending e-mail messages from the database engine. The default status for this feature is disabled. You can enable Database Mail from Management Studio (right-click on Database Mail under the Management folder), or in a query window run:
  

  EXEC sys.sp_configure 'Database Mail XPs', 1;

  
  Once Database Mail has been enabled and configured with an account and a profile, SQL Agent can use it to send email notifications for SQL Agent alerts and SQL Agent jobs.

• SQL Agent Jobs are the means to execute scheduled tasks in SQL Server. I always create a job for every SQL Server instance that cycles the SQL Server Error log and SQL Agent Error Log, and a second job that performs cleanup operations on the Database Mail tables in msdb. These scripts are attached here:
  
  JR_TSQL2sday.zip

  You might also be interested in Tim Radney's recent post, The Importance of Maintenance on MSDB, for other areas to focus your automated cleanup routines.

Summary

SQL Server Agent is a powerful resource waiting to be used. It's up to us DBAs to tap into its potential.