Rebuilding MSDB on SQL2K

1. In SQL Server Enterprise Manager, right-click the server name andclick Properties.
2. On the General tab, click Startup Parameters.
3. Add a new parameter as "-T3608" (without the quotation marks).
4. Stop, and then restart SQL Server.
5. Make sure that the SQL Server Agent service is not currently running.
6. Detach the msdb database as follows:use master GO sp_detach_db 'msdb' GO
7. Delete or rename the msdb mdf and ldf files.
8. Run the INSTMSDB.SQL script
9. Remove the -T3608 trace flag from the startup parameters box in Enterprise Manager
10. Stop, and then restart SQL Server.

MSDB Database is HUGE... try deleting old backup history

We recently ran into a problem with the MSDB database on one of our servers getting too big.

We have about 30 databases on this particular server, and dumpo transaction logs every 15 minutes on many of them. We have backup history clear back to 1998. Since our backup/restore plan only includes going back as far as 60 days, thevast majority of this backup history is obsolete.

So... Microsoft has supplied a wonderful little stored procedure: sp_delete_backuphistory

The problems are:

1. The code is poorly written
2. Any hits against the backup history tables will be table scans since there are no indexes

So, if you run EXEC sp_delete_backuphistory '07/07/07' it will literally take forever. I clocked mine at about 5 rows per minute.

In order to fix this, an index needs to be created on msdb..backupset. MS does not like it when you modify their system tables, so you can't use Enterprise Manager to do this.

Run the following in Query Analyzer:

USE msdb

CREATE INDEX temp_media_set_id

ON backupset (media_set_id)

GO

This took a while to run, but will save you a TON of time when you finally run the sp again:

EXEC sp_delete_backuphistory '07/07/07'