Backup and Restore is arguably the most important task a DBA can do, but sometimes it is very frustrating to manage these elements. Some common problems include very slow UI interaction, both for SQL Enterprise Manager and for SQL Server Management Studio, mysterious timeouts, failure to delete old backup files, and other intermittent failures. Most of these failures can be traced back to the same underlying problem and thus have the same solution.

The MSDB database contains a series of system tables that track everything having to do with backups on that server. One of the limitations of vendor testing is that no single test platform ever runs for anything like the lifetime of a typical production SQL Server. Because of this, no test server accumulates the history in the MSDB tables that real-world systems do. Since SQL uses its standard table storage and retrieval mechanisms to store this information, scalability should not be a problem. Wrong. Just as with user tables, system tables will perform poorly if they are not correctly indexed. Now for the real kicker:

There are no indexes on the MSDB backup tracking tables.

That is right. Not a single index. No Referential Integrity, nothing.

In SQL 2000, this is even worse as there is no MSDB cleanout component in the maintenance plan wizard. SQL 2005 has it in SP2 and higher releases.

Fixing this problem is actually fairly easy. Here is a script to create indexes on the MSDB backup tables. This handles the basic performance issue, however it is still a good idea to clean out the old backup information. Unless you have an amazing archive system, tracking a transaction log backup from three years ago will not do you much good.

/************************************************************************

*                                                *

*    Title:    msdb index creation                        *

*    Author:    Geoff N. Hiten                            *

*    Purpose: Index msdb database                            *

*    Date:    12/12/2005                                    *

*    Modifications:                                    *

*                                                *

*    01-01-2001                                        *

*        Sample Entry                                *

*                                                *

*                                                *

************************************************************************/

use msdb

go

--backupset

Create index IX_backupset_backup_set_id on backupset(backup_set_id)

go

Create index IX_backupset_backup_set_uuid on backupset(backup_set_uuid)

go

Create index IX_backupset_media_set_id on backupset(media_set_id)

go

Create index IX_backupset_backup_finish_date on backupset(backup_finish_date)

go

Create index IX_backupset_backup_start_date on backupset(backup_start_date)

go

--backupmediaset

Create index IX_backupmediaset_media_set_id on backupmediaset(media_set_id)

go

--backupfile

Create index IX_backupfile_backup_set_id on backupfile(backup_set_id)

go

--backupmediafamily

Create index IX_backupmediafamily_media_set_id on backupmediafamily(media_set_id)

go

--restorehistory

Create index IX_restorehistory_restore_history_id on restorehistory(restore_history_id)

go

Create index IX_restorehistory_backup_set_id on restorehistory(backup_set_id)

go

--restorefile

Create index IX_restorefile_restore_history_id on restorefile(restore_history_id)

go

--restorefilegroup

Create index IX_restorefilegroup_restore_history_id on restorefilegroup(restore_history_id)

go

/************************************************************************

*    End Script                                        *

************************************************************************/

Notice that these are performance indexes only; they do not enforce any constraints.

For SQL 2000 users, there is one more bit of work to do. You need to schedule a job to clean out MSDB backup history on a regular basis. SQL 2005 users can set the maintenance plans to handle this. The command is: