Why aren't my SQL Server 2005 backups being deleted?

Question

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.

Answer 1

My understanding is that you can only include the first level of subfolders. I am assuming that you have that check-box checked already.

Are your backups deeper than the just one level?

Another thought is, do you have one single maintenance plan that you run to delete backups of multiple databases? The reason I ask this is because the way I could see that you would have to do that would be to point it to a folder that was one level higher meaning that your "include first-level subfolders" would not be deep enough.

The way I have mine set up is that the Maintenance Cleanup Task is part of my backup process. So once the backup completes for a specific database the Maintenance Cleanup Task runs on that same database backup files. This allows me to be more specific on the directory so I don't run into the directory structure being too deep. Since I have the criteria set the way I want, items don't get deleted till I am ready for them to be deleted either way.

Tim

Answer 2

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.

Answer 3

Make sure your maintenance plan does not have any errors associated it with. You can check the error log under the SQL Server Agent area in the SQL Server Management Studio. If there are errors during your maintenance plans, then it is probably quitting before it starts to delete the outdated backups.

Answer 4

Another issue could be the "workflow" of the maintenance plan.

If your plan consists of more than one task, you have to connect the tasks with arrows to define the order in which they will run.

Possible issue #1:

You forgot to connect them with arrows. I just tested that - the job runs without any error or warning, but it executes only the first task.

Possible issue #2:

You defined the workflow in a way that the cleanup task will never run. If you connect two tasks with an arrow, you can right-click on the arrow and specify if the second task will run always or only when the first one does/does not run successful (this changes the color of the arrow, possible are red/green/blue). Maybe the backup works, and then the cleanup never runs because it will only run when the backups fails?