Australian cloud for business software — built and run in Sydney since 2002

NetLeverage
NetLeverage

SQL Server Maintenance Cleanup Task not deleting old backups, and the backup disk fills

Technical article
Backup & Recovery

About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.

When the Maintenance Cleanup Task leaves old backup files behind, check its own settings first: the folder, the file extension, whether subfolders are included, or the age threshold. The task deletes only files that match all of them. A second group of causes sits in how the plan is wired, because the cleanup may never run, or may run at the wrong moment, if an earlier task fails. Prove which one applies before you change retention, because a wrong change here deletes backups.

What the error means

A misconfigured cleanup task can run with no error message at all. The plan reports success, and the backup volume keeps growing. Microsoft describes the task as one that removes "old files related to maintenance plans, including text reports created by maintenance plans and database backup files" (Maintenance Cleanup Task). The behaviour is entirely driven by its options.

The documented rules that matter most:

  • Extension. In "Search folder and delete files based on an extension" mode, the File extension field takes the extension, and Microsoft says: "Don't include any other characters before or after the extension (for example, use BAK or TRN)."
  • Subfolders. The task "does not automatically delete files in the subfolders of the specified directory." To reach files in first-level subfolders you must select Include first-level subfolders. The name matters: only the first level.
  • Age. You can "delete files based on the age of the file at task run time", with a number and a unit of Day, Week, Month or Year. Files older than that are deleted.
  • Location. A note on the page says the task "removes files locally stored on the server."
  • What a plan is. A maintenance plan creates an Integration Services package that is run by a SQL Server Agent job (Maintenance plans). The tasks are joined by precedence constraints, each of which can be Success, Failure or Completion (Precedence constraints). With a Success constraint the next task runs only if the previous one succeeded.

The Back Up Database Task adds one more fact. If you tick Create a sub-directory for each database, the backups go into per-database subfolders. Microsoft warns that "maintenance tasks cannot delete subdirectories", which is described as a defence against abuse of the cleanup task. Files can be removed from those subfolders, but the empty folders stay (Back Up Database Task).

Example (illustrative): on SQL01, a plan called Nightly_Full backs up 40 databases into D:\Backup\<database name>\ and takes about 120 GB of backups per night. The cleanup task points at D:\Backup, extension bak, subfolders not included, 14 days. It deletes nothing, because every file sits one level down. Fourteen days of retention should hold about 1,680 GB (120 GB x 14). With no deletion, a 4,000 GB volume fills in about 33 days (4,000 / 120).

Likely causes

Cause What you see How to confirm
Backups are in subfolders and Include first-level subfolders is off Files in D:\Backup\<db>\ never removed, files directly in D:\Backup removed Open the cleanup task, compare its folder with the real path in the backup history, and check whether the box is ticked
Backups are two or more levels below the folder Even with subfolders ticked, nothing is removed Compare the folder depth in the backup history with the task's folder
Extension field has extra characters (for example a leading dot) Nothing matches Read the field; Microsoft says to use BAK or TRN with nothing before or after
Extension does not match the files (bak task, .trn log files, or a third-party extension) Some file types removed, others not List the extensions actually present in the folder
Task is in the wrong mode (a specific file, or maintenance plan text reports instead of backup files) The task runs and deletes unrelated or no files Open the task and check which option is selected
Age threshold longer than you think, or unit wrong (Month or Year instead of Day) Old files exist but are younger than the threshold Compare the oldest file's age with the configured number and unit
Cleanup is downstream of a task that fails, through a Success constraint The earlier task fails, so the cleanup never runs Read the plan's job and plan history for failures; see below
Cleanup runs even when the backup failed (Completion constraint) Old backups deleted while no new backup was written Check the constraint type on the line between the backup and the cleanup task
Backups written elsewhere than the cleanup folder Cleanup folder holds nothing; the full volume is elsewhere Query the actual backup paths in msdb
The task fails or the operating system refuses a delete Errors in plan or job history Open the job history and the maintenance plan history

Prove the cause before changing anything

All of the following are read-only. They follow the Microsoft pages cited and have not been run or lab-tested.

  1. Find where the backups really are. The msdb tables backupset and backupmediafamily record every backup and its physical_device_name. This is a cut-down version of Microsoft's own example query:
SELECT bs.database_name, bs.type, bs.backup_start_date,
       bf.physical_device_name
FROM msdb.dbo.backupset AS bs
JOIN msdb.dbo.backupmediafamily AS bf
  ON bs.media_set_id = bf.media_set_id
ORDER BY bs.backup_start_date DESC;

type is D for a full backup, I for differential and L for log. The physical_device_name column can hold an original backup or restore path, so use it as a guide and confirm in the file system.

  1. See exactly what the task would do. The task dialog has a View T-SQL button that shows the statements it will run for the options selected.
  2. Read the history. In Management Studio, right-click Maintenance Plans and choose View History. Results are written to sysmaintplan_log and sysmaintplan_logdetail in msdb. Also read the SQL Server Agent job history for the plan's job.
  3. Check the wiring. Open the plan on its design surface. Note the type of the line into the cleanup task (Microsoft documents Success as green, Failure as red and Completion as blue in SSIS Designer; confirm the labels in your own tool) and whether the cleanup comes before or after the backup.
  4. Compare in the file system. Count files by extension and folder depth. Note the oldest file's date.

Fix

Deletion is permanent. Before widening the folder, adding subfolders or shortening the age, list the files that would match and make sure the newest full backup and the log chain that depends on it are safe. The first two checks below are the safe ones.

  • Subfolders. Tick Include first-level subfolders if backups sit one level down. If they are deeper, point the task at the folder directly above the files, or use a separate cleanup task per level. Empty database folders will remain, and Microsoft documents that maintenance tasks cannot delete them.
  • Extension. Enter BAK for full and differential backups, and TRN for logs, with nothing else in the field. Use a separate task for each extension you need removed.
  • Mode and age. Choose the mode "Search folder and delete files based on an extension" and select Backup files. Set the number and unit deliberately. Remember that age is measured at the time the task runs.
  • Order and constraints. Place cleanup after the backup, and connect them with a Success constraint, so a failed backup does not trigger deletion of the last good copy. Microsoft's definitions are that Success "requires that the precedence executable must complete successfully", while Completion means the constrained task runs "regardless of whether the precedence task succeeds or fails". Use Completion only if you have another way to guarantee a good backup exists.
  • Wrong path. Correct the folder in the cleanup task to match the paths in the backup history.
  • Failures. Fix the underlying error shown in the plan or job history. The article on SQL Agent job outcomes explains how a job can look successful when a step failed.

If you want a single check on the logic, ask: after the cleanup runs, will there still be a complete, tested restore chain for the retention period you promised? Where that is a recovery target rather than a disk-space target, size the volume for it. See the article on retention capacity measurements.

Confirm the fix

  • After the next scheduled run, files older than the threshold are gone from every folder you expected, and files younger than it remain.
  • Free space on the volume stops falling run over run, and begins to hold steady near the level your retention period implies (the number of days kept times the daily backup size).
  • The plan history shows the cleanup task completed without errors.
  • A restore test of the oldest backup you intended to keep, and of the newest, succeeds on another server. Freed disk space proves nothing about restorability; see the article on proving a SQL Server backup will restore.

What to watch afterwards

  • Alert on free space on the backup volume, not only on plan success.
  • Once a month, compare the age of the oldest file with the retention you intended. If it is far older, the cleanup is not doing its job.
  • Remember that deleting files does not remove their records. sp_delete_backuphistory in msdb removes history rows older than a date, and Microsoft states "the physical backup files are preserved, even if all the history is deleted." Run it for history size, but do not expect it to free disk space (sp_delete_backuphistory). Equally, a long history does not mean files still exist.
  • Treat any change to the backup folder, the use of per-database subfolders, or the backup extension as a trigger to recheck the cleanup task.

Risks and limits

  • These queries and steps follow Microsoft documentation. They have not been run or lab-tested.
  • The documentation covers SQL Server 2017 to 2025 on Windows. Other products' backup tools have their own retention rules.
  • Microsoft's page says what happens with an extension written as BAK or TRN. It does not describe the result of a leading dot, so the advice here is to follow the page's instruction rather than to rely on any particular behaviour.
  • The page says the age is "the age of the file at task run time". It does not say whether that comes from the file's modified date or another timestamp. Check against your own files.
  • Microsoft does not say which account performs the file deletions, or how the task treats network paths. The note that files are "locally stored on the server" is the only statement. Confirm in your own environment if you back up to a share.
  • Deleting backups is irreversible unless another copy exists.

Sources