I just about ran out of space on my Data partition and discovered that the msdb database was over 114 GB! In particular, the backupfile table had over 107,000,000 rows which took up over 70 GB. Other backup-related tables took up the rest of the space. This is a real problem. The normal command I would run to purge backup history did not work because it uses transactions and would fill up TLog. So I had to run the sp in a look one day at a time:
SET NOCOUNT ON
DECLARE @purge_date DATETIME
DECLARE @cutoff_date DATETIME
— Set purge cutoff to n days back
set @cutoff_date = DATEADD(dd,DATEDIFF(dd,0,GETDATE())-180,0)
PRINT ‘Start Date: ‘ +
CONVERT(VARCHAR(10),GETDATE(),121)
PRINT ‘Purge backup history before Cutoff Date: ‘ +
CONVERT(VARCHAR(10),@cutoff_date,121)
WHILE 1 = 1
BEGIN
SET @purge_date = null
— Find date of oldest backup set
SELECT @purge_date = DATEADD(dd,DATEDIFF(dd,0,MIN(backup_finish_date))+1,0) FROM msdb.dbo.backupset(NOLOCK)
WHERE backup_finish_date <= @cutoff_date
IF @purge_date is null or @purge_date > @cutoff_date
BEGIN
PRINT ‘Purge backup history complete through: ‘+
CONVERT(VARCHAR(10),@cutoff_date ,121)
BREAK
END
PRINT CHAR(10)+CHAR(13) +
‘Purging backup history before: ‘ +
CONVERT(VARCHAR(10),@purge_date,121) +CHAR(10)+CHAR(13)
SELECT [Backup Sets to be Deleted Count ] = COUNT(*) FROM msdb.dbo.backupset(NOLOCK)
WHERE backup_finish_date < @purge_date
EXEC msdb.dbo.sp_delete_backuphistory @purge_date
END
PRINT ‘End Date: ‘ +
CONVERT(VARCHAR(10),GETDATE(),121)
