SQL Server™ 2005 Bible

Chapter 38. Automating Database Maintenance with SQL Server Agent

In This Chapter

• Setting up SQL Server Agent

• Understanding alerts, operators, and jobs

• Managing alerts

• Managing operators

• Managing jobs

The automation of database maintenance is crucial to ensuring that a database is regularly checked, maintained, and optimized. Automated checking consists of monitoring database size to identify issues before they generate mayhem; maintenance includes frequent backups; and optimization involves tweaking the index configuration for optimal performance. Automation ensures that these activities do not consume too much of your time, so you can focus on more pressing issues (such as improving your golf game, perhaps).

Ideally, you want SQL Server to monitor itself and alert you when it encounters a critical condition. Luckily for you, Microsoft grants this specific wish, because SQL Server 2005 includes a powerful component that can send alerts when specific critical conditions occur. Better still, this same component also enables you to schedule routine maintenance tasks either on a one-time basis or on a recurring basis—for example, once a month or, say, on the first Saturday of every month. SQL Server Agent is the service responsible for processing alerts and running scheduled jobs.

Setting Up SQL Server Agent

Setting up SQL Server Agent is straightforward, as long as you avoid two pitfalls: The first is rather elementary, the second a bit more subtle. We'll cover the easy one first. Because ...