Why aren’t the SQL Server 2005 backups being deleted


I've got a maintenance plan that executes weekly in the off hours. It's always reporting success, but the old backups don't get deleted. I don't want the drive filling up.

DB Server info: SQL Server Standard Edition 9.00.3042.00

There is a "Maintenance Cleanup Task" set to

"Search folder and delete files based on an extension"

and "Delete files based on the age of the file at task run time" is checked and set to 4 weeks.

The only thing I can see is that my backups are each given their own subfolder and that this is not recursive. Am I missing something?

Also: I have seen the issues pre-SP2, but I am running service pack 2.

Best Solution

If you make your backups in subfolders, you have to specify the exact subfolder for deleting.

For example:

You make the backup by choosing the option that says something like "Make one backup file for each database" and check the box that says "Create subfolder for each database". (I work with a German version of SQL Server, so I translate everything into English myself now)

The specified folder is H:\Backup, so the backups will actually be created in the folder H:\Backup\DatabaseName.

And if you want the Maintenance Cleanup Task to delete the backups via "Delete files based on the age of the file at task run time", you have to specify the folder H:\Backup\DatabaseName, not H:\Backup !!!

This is the mistake that I made when I started using SQL Server 2005 - I put the same folder in both fields, Backup and Cleanup.