SQL Server Tuning: Database Maintenance
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Introduction
Maintaining a healthy database involves more than a single tuning effort. With SQL Server 2000, Microsoft automated many tedious maintenance and tuning steps. However, it would be unreasonable to ask for a database that tunes itself as it pleases. Computers don't know how to read our minds just yet. This article explores some steps you need to take to keep your database's health in check.
NOTE
Maintaining databases is the responsibility of database administrators (DBAs). This article assumes that you're familiar with some of the basic DBA tasks.
Backups
It doesn't take a genius to figure this out: Whether you have tuned your database or not, you need a reliable backup strategy. Otherwise, you risk losing your database (and your job) altogether. Frequency and type of backups you need to perform depend largely on the database application type.
Following is a very-high-level overview of possible backup strategies. Since this is a tuning article, we won't go into great detail on what's involved in each type of backup.
If you have a transactional database, you should take full database backups every night and transaction log backups as often as possible. The full database backup will slow the response time to a certain degree, so it's best to perform such backups during the hours of limited user activity. Transaction log backups don't affect performance as much as full backups, as long as the log and data files reside on different physical disks. Occasionally you might also benefit from differential backups, which back up only the portion of the database that has changed since the last full backup.
With a reporting application, you shouldn't have any write activity, so the chances of losing data are limited to hardware failures. Generally you don't need to back up transaction logs of a reporting database—taking a backup after the database is populated will suffice. Similarly, data warehouses need to be backed up immediately before and after population to ensure that the data import routine doesn't break anything.
It's important to keep database backups in a safe place. Many organizations have separate holding facilities for backups. It's also important to keep backups where you can get to them quickly in an emergency.