Why aren't my SQL Server 2005 backups deleted?

Question

I have a maintenance plan that runs weekly after hours. He always reports success, but old backups are not deleted. I do not want the drive to fill up.

Database server information: SQL Server Standard Edition 9.00.3042.00

There is a “maintenance cleanup task” set to

"Search for folders and delete files based on extension"

and "Delete files depending on the age of the file during the execution of the task" is installed and installed for 4 weeks.

The only thing I see is that my backups are provided with each individual subfolder and that it is not recursive. Did I miss something?

Also: I saw problems with pre-SP2, but I am running Service Pack 2.

Answer 1

If you create backups in subfolders, you must specify the exact subfolder for deletion.

For instance:

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

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

And if you want the Maintenance Cleanup task to delete backups through "Delete files depending on the age of the file while the task is running", you need to specify the folder H: \ Backup \ DatabaseName , not H: \ Backup !!!

This is the mistake I made when I started using SQL Server 2005 - I put the same folder in both fields: “Backup and cleanup”.

Answer 2

I understand that you can only include the first level of subfolders. I assume you already have this check box selected.

Are your backups deeper than one level?

Another thought: do you have one maintenance plan that you run to delete backups of multiple databases? The reason I ask about this is because, as I could see that you would need to do this, you need to point it to a folder that was one level higher, which means that your "include first level subfolders "won't be deep enough.

The way I set up is that Task Cleanup Task is part of my backup process. Thus, after the backup for a specific database is completed, the Maintenance Cleanup task is launched in the same database backup files. This allows me to be more specific in the directory, so I do not come across too deep a directory structure. Since I have criteria set as I want, the elements are not deleted until I am ready to delete them anyway.

Tim

Answer 3

Make sure your service plan has no errors associated with it. You can check the error log in the SQL Server Agent scope in SQL Server Management Studio. If there are errors during your maintenance plans, this may happen before it starts deleting obsolete backups.

Answer 4

Another problem may be the “workflow” of the service plan.

If your plan consists of several tasks, you need to combine the tasks with the arrows to determine the order in which they are launched.

Possible problem number 1:

You forgot to associate them with the arrows. I just tested this: the work is done without any errors or warnings, but it only performs the first task.

Possible problem number 2:

You have defined the workflow so that the cleanup task never starts. If you connect two tasks with an arrow, you can right-click on the arrow and indicate whether the second task will always be executed or only when the first does / does not work successfully (this changes the color of the arrow, possibly red / Green, blue) . Perhaps the backup is working and then the cleanup never starts because it will only work if the backup fails?