Backup files leave the retention folder, but their history keeps accumulating in msdb. Regular sp_delete_backuphistory cleanup gives that metadata a deliberate retention window instead of an unlimited lifetime.
Measure the Metadata Before Removing It
SQL Server records backup and restore activity in msdb tables. Backupset is only part of that collection. Related file, media, and restore records also consume space. An oversized history collection can make reports and metadata work expensive, including activity performed during backup operations. Measure it before choosing the cleanup scope.
I inspect row populations and storage allocation first. Large msdb can also contain job history, mail records, and other metadata. A backup-history cleanup does not explain or remove every source of growth. Identify the tables responsible instead of treating the database size as one undifferentiated problem.
USE msdb;
GO
SELECT t.name AS TableName,
SUM(p.row_count) AS ApproximateRows,
SUM(p.reserved_page_count) * 8.0 / 1024 AS ReservedMB,
SUM(p.used_page_count) * 8.0 / 1024 AS UsedMB
FROM sys.tables AS t
JOIN sys.dm_db_partition_stats AS p ON p.object_id = t.object_id
WHERE p.index_id IN (0,1)
AND t.name IN ('backupset','backupfile','backupfilegroup',
'backupmediafamily','backupmediaset',
'restorehistory','restorefile','restorefilegroup')
GROUP BY t.name
ORDER BY ReservedMB DESC;The row counts are metadata estimates. The selected heap or clustered partitions describe base-table allocation, excluding separate nonclustered indexes. Include all index partitions when reviewing total allocated storage. State that scope in your report so a base-table estimate does not masquerade as the entire history footprint.
Choose a Retention Window With a Reason
Twelve months is a useful starting proposal for an estate that needs seasonal troubleshooting and annual comparisons. Adjust it to documented recovery, audit, and operational requirements. Some environments need a longer record, while others retain historical evidence separately and need less online metadata. Approve the window before automating deletion.
Keep enough history to support the backup chains you routinely investigate. Include irregular restores, retired databases, and infrequent maintenance. The metadata window should explain current recoverability and recent operational behavior. It does not itself determine how long actual backup files or encryption keys must remain available.
A cleaner history table has no opinion about a missing restore dependency. Coordinate history retention with backup-file inventory and recovery procedures. Retain essential evidence outside msdb when the agreed process requires it. Document both the online window and the location of any longer-lived accepted records.
Preview the Proposed Date Boundary
Calculate the cutoff using the same local date convention as the recorded history. Count backup sets before that cutoff and review their oldest and newest dates. These are candidates within one history table, rather than a prediction of every row the cleanup procedure will affect.
DECLARE @OldestDate datetime = DATEADD(MONTH, -12, GETDATE());
SELECT @OldestDate AS ProposedCutoff,
COUNT_BIG(*) AS CandidateBackupSets,
MIN(backup_start_date) AS EarliestStart,
MAX(backup_finish_date) AS LatestFinish
FROM msdb.dbo.backupset
WHERE backup_finish_date < @OldestDate;Check databases with unusual retention needs before accepting an instance-wide cutoff. Record the selected value with the approval. The procedure removes related backup and restore metadata, so reviewing only one report's visible rows can understate the scope. A deletion plan should describe the relationships it will clean.
Run sp_delete_backuphistory as the Supported Cleanup
Use the system procedure rather than hand-deleting rows from its related tables. It coordinates the history cleanup through supported logic. Run with appropriate permissions in a reviewed window. Back up msdb before a substantial first cleanup so the operational metadata has a recovery path.
USE msdb;
GO
DECLARE @OldestDate datetime = DATEADD(MONTH, -12, GETDATE());
EXEC dbo.sp_delete_backuphistory @oldest_date = @OldestDate;This removes history, not physical backup files. It does not delete a .bak or .trn from storage. Conversely, deleting files does not automatically remove their history. Those two retention operations need separate controls and verification. Avoid using a report's continued presence as proof that its file still exists.
The initial cleanup can create substantial log activity and contend with other msdb work. Test its duration and resource behavior on an appropriate copy when the collection is large. Keep regular cleanup frequent enough that future runs process a bounded backlog. An annual cleanup job has a remarkable ability to become an annual surprise.
Schedule sp_delete_backuphistory in a Weekly Agent Job
Create the schedule after the manual procedure and retention policy are accepted. This example uses Sunday at 2:00 a.m. in the server's local time. Select your own low-activity window. Confirm that SQL Server Agent is running and that the job owner has the required execution rights.
USE msdb;
GO
DECLARE @JobID uniqueidentifier;
EXEC dbo.sp_add_job @job_name = N'Backup History Cleanup',
@enabled = 1, @job_id = @JobID OUTPUT;
EXEC dbo.sp_add_jobstep @job_id = @JobID,
@step_name = N'Retain twelve months', @subsystem = N'TSQL',
@database_name = N'msdb',
@command = N'DECLARE @Cutoff datetime = DATEADD(MONTH,-12,GETDATE());
EXEC dbo.sp_delete_backuphistory @oldest_date=@Cutoff;';
EXEC dbo.sp_add_jobschedule @job_id = @JobID,
@name = N'Weekly Sunday', @freq_type = 8,
@freq_interval = 1, @freq_recurrence_factor = 1,
@active_start_time = 020000;
EXEC dbo.sp_add_jobserver @job_id = @JobID;Use a new job name in a test instance when rehearsing the script. It is a creation example, not an idempotent update script for an existing job. SQL Server Express does not include Agent. Its scheduling arrangement needs a separate approved execution path, rather than a job that never runs.
Treat One-Database Cleanup Differently
The database-specific procedure removes that database's backup and restore history without an age cutoff. It is appropriate for a deliberately retired database's metadata after retention review. It is not a narrower twelve-month cleanup. Read the scope before choosing it for an active database.
USE msdb;
GO
EXEC dbo.sp_delete_database_backuphistory
@database_name = N'RetiredLabDatabase';Replace the demonstration name only after verifying the intended database identity and retention decision. Reused names complicate that review. A procedure scoped by database name cannot understand your historical naming policy. Keep approved records before deleting history needed to distinguish earlier databases with the same name.
Verify the sp_delete_backuphistory Job and Reusable Space
I check the next scheduled run and its outcome after installing retention automation. Confirm the cutoff behavior and inspect collection growth over time. A successfully created job does not prove a successful cleanup. Alert on failures and review the job after engine upgrades or permission changes.
Which record would you need during the next recovery investigation? Keep that need visible when tuning the retention window. The sp_delete_backuphistory procedure frees reusable database space but does not automatically shrink the physical msdb files. Let stable recurring cleanup control growth before considering any separate exceptional file-size change.
Use sp_delete_backuphistory through an identity with the required permissions and retain job failure evidence. A timeout needs investigation of blocking, log capacity, and workload overlap. Do not disable the actual backup schedule to make metadata maintenance appear successful. Choose a quieter cleanup window and monitor the retry so both operations remain dependable.
Use the same size and age queries after cleanup to verify its intended effect. Keep file-retention verification separate. That gives msdb a controlled history window without confusing a tidier catalog with a completed recovery strategy.
Related reading on this blog: Backup Retention: How Long to Keep What and Estimating Table Growth for Next Year From Backup History.
History cleanup is not backup-file deletion, it is retention management for the recovery catalog.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
