Chapter 43. 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

  • Setting up Database Mail

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, SQL Server can monitor itself and send alerts when it encounters a critical condition. Luckily Microsoft grants this specific wish, because SQL Server includes a powerful component that can send alerts when specific critical conditions occur. Better still, this same component also enables the scheduling of 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.

Note

SQL Server Agent service is not available in SQL Server Express Edition, for which scheduling of jobs has to be done using Windows Scheduler. For more information on Windows Scheduler, search the web, using ...

Get Microsoft® SQL Server® 2008 Bible now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.