SQL Server Maintenance

< Previous section  |  Table of Contents  |  Index  |  Next section >

From time to time, you should clean up your SQL Server database. Clean-up operations can include reorganizing the database index, rebuilding the index from scratch, and various other actions.

Note: SQL Server uses various working files in its processes. It keeps its working files around until the main database files get backed up, just in case the computer crashes and SQL Server has to try to reconstruct everything that's happened since the last backup.

For this reason, you shouldn't back up your database just by making a simple copy of the database file. You must use some technique which tells SQL Server, "We've made a good backup, so you can get rid of the working files." Otherwise, the working files will continue to grow and grow. Most commercial backup software has facilities that can work with SQL Server databases in the appropriate way.

If you have a full SQL Server (as opposed to SQL Server Express), you can arrange clean-up operations through SQL Server Management Studio. To do so, follow these steps:

  1. Login to an account with Windows Administrator privileges.
  2. Start SQL Server Management Studio. (On Windows 7 and Vista, you should use Run as Administrator.)
  3. In the left-hand panel, expand the entry for Management.
  4. Right-click the entry for Maintenance Plans, and click Maintenance Plan Wizard.

The wizard will then walk you through the process of creating a maintenance plan that performs various operations automatically. During the process, you can choose what operations will be performed. As a minimum, we recommend the following:

Check Database Integrity
Reorganize Index
Rebuild Index
Clean Up History
Maintenance Cleanup Task

(You can also include a backup operation, although you should already be backing up your database with a different maintenance plan—you don't need to clean your database as often as you need to back it up.)

Once you've used the wizard to create the maintenance plan, you can execute the operations by right-clicking the entry for the maintenance plan, then clicking Execute.

If you ever have a system crash or power failure on the server computer, you should make a point of cleaning up your database as soon as possible afterward.

If you have SQL Express, the associated Management Studio does not allow you to make maintenance plans.

< Previous section  |  Table of Contents  |  Index  |  Next section >