Sunday 21 August 2011

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 &amp;lt; @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;

3 comments:

Matthew Tessier said...

While I agree that you don't need to keep bakup history forever, it can be valuable data. For example, since backups are usually taken on a regular basis and the bakup history has the size of the backup file created over time, you can calculate growth estimates to see when more data file or disk space is going to be needed.

Matthew Tessier

r5d4 said...

Hi Matthew,

Totally valid points. When servers are mine to monitor I do have a tools db which records db growth, table growth, fragmentation etc manually.

The scenario I'm addressing in this post is the absolute extreme. Sites where no one has done ever looked at msdb growth and the server was built in 2004 ! An example >

In 24 hours a single db with TL backups every 30 minutes generates 48 rows in the table. The daily full backup generates another.

15 user dbs on the server are backed up in this way. 15 & 49 = 735 rows a day. Master, Msdb and Model also being backed up daily gives another 3 rows, total 738.

7 (years) * 365 (days) * 738 (records) = 18876340 rows.

Given that Msdb in SQL 2000 shipped with no indexes on the backup tracking tables, Enterprise Manager grinds to a halt if you attempt to view backup history.

Add to this that msdb is growing in increments of 1MB and is highly physically fragmented over a drive.

It's an extreme situation, but one I've come up against several times recently hence the effort and post about it.

Rich

AMBhatti said...

Just wanted to drop a thank you note!!
Ran into an issue on a log shipping database where the MSDB database had grown to 16Gb in size. After using your "Clear Backup History " to do some pruning on the main culprit of sysjobhistory, I put your "history cleanup" job in place to keep the MSDB tidy.

Cheers.