Cleaning up MSDB

I've been working extensively with old SQL 2000 instances recently. The majority have been left to fend for themselves without DBA support. Whilst backups and index maintenance have for the most part been occuring, the msdb databases are bloated (and fragmented). This is due to some missing functionality in SQL 2000 , namely clearing down the history tables. This post summarises my previous ones on MSDB clearup.

1) Backup History

To determine the date of the oldest backup history record, run this -

SELECT TOP 1 backup_start_date FROM msdb.dbo.backupset WITH (NOLOCK) ORDER BY backup_set_id ASC

If there is significant history to remove, use the Ultimate Delete Backup History Script to remove it. To prevent it building up again in the future, regularly run (or schedule) -

DECLARE @BackupHistoryDeleteDate DATETIME
SET @BackupHistoryDeleteDate = DATEADD(m,-1,GETDATE())
EXEC msdb.dbo.sp_delete_backuphistory @BackupHistoryDeleteDate ;

2) Agent Job History

To determine the date of the oldest agent history record, use this -

DECLARE @agentstartdate DATETIME
SELECT @agentstartdate = CONVERT(datetime,CONVERT(VARCHAR(8),MIN(Run_date))) from msdb..sysjobhistory WITH (NOLOCK) OPTION (MAXDOP 1)

If there is significant history to remove, use the Clear Job History (Nibble Delete) Script to remove it.
To prevent it building up again in the future, regularly run (or schedule) -
For SQL 2000 -

DECLARE @JobHistoryDeleteDate DATETIME
SET @JobHistoryDeleteDate = DATEADD(m,-1,GETDATE())
DECLARE @datepart INT
SET @datepart = CONVERT(INT, CONVERT(VARCHAR, @JobHistoryDeleteDate, 112))
DELETE FROM msdb.dbo.sysjobhistory WHERE (run_date < @datepart)

For SQL 2005+ you can use sp_purge_jobhistory (it now takes a date parameter), hence regularly run -

DECLARE @JobHistoryDeleteDate DATETIME
SET @JobHistoryDeleteDate = DATEADD(m,-1,GETDATE())
EXEC sp_purge_jobhistory @oldest_date = @JobHistoryDeleteDate 

You can also set a row limit by using this registry entry.

3) Maint Plan History

To determine the date of the oldest maintenance plan record, use this -

DECLARE @mpstartdate DATETIME
SELECT @mpstartdate = MIN(end_time) FROM msdb.dbo.sysdbmaintplan_history WITH (NOLOCK) OPTION (MAXDOP 1)

If there is significant history to remove, use the Clear Maintenance Plan History (Nibble Delete) Script to remove it. To prevent it building up again in the future, regularly run (or schedule) -

DECLARE @MaintPlanHistoryDeleteDate DATETIME
SET @MaintPlanHistoryDeleteDate = DATEADD(m,-1,GETDATE())
DELETE FROM msdb.dbo.sysdbmaintplan_history WHERE (end_time < @MaintPlanHistoryDeleteDate 

To keep all 3 tables under control, here is a script I schedule on my SQL 2000 instances, to keep MSDB in shape

-- Keep MSDB Tidy
-- This script keeps 3 tables that have a tendency to bloat down to 2 weeks data
-- It also Cycles error logs when it is executed

DECLARE @HistoryDeleteDate DATETIME
SET @HistoryDeleteDate = DATEADD(week,-2,GETDATE())

-- 1) Backup History
EXEC msdb.dbo.sp_delete_backuphistory @HistoryDeleteDate ;

-- 2) Agent Job History
DECLARE @datepart INT
SET @datepart = CONVERT(INT, CONVERT(VARCHAR, @HistoryDeleteDate, 112))
DELETE FROM msdb.dbo.sysjobhistory WHERE (run_date < @datepart)

-- 3) Maint Plan History
DELETE FROM msdb.dbo.sysdbmaintplan_history WHERE (end_time < @HistoryDeleteDate) 

-- 4) Cycle SQL Server Error logs
EXEC master.dbo.sp_cycle_errorlog;