sql-server-2005-backups-not-being-deleted

Question

I have a customer running SQL Server 2005 under a Windows 2008 Server. Even though the Maintenance plan completes sucessfully with No errors, the backup files do not get deleted.

Here is the subplan command, it is set to delete files over a day old: EXECUTE master.dbo.xp_delete_file 0,N''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup'',N''.bak'',N''2009-06-16T23:06:05'',1

The backup command and the delete command point to the same location. The backup works, not the delete. What exactly does the checked box that says "include first level sub folders" do? What color arrow should there be between the task? Success or Completion?

Thank You.

Answer 1

Include first level subfolders ensures that the delete command looks in subdirectories of the fold that you point it to. This is important if (for instance) you backup to a folder called "Backups", but each individual database's backups reside in their own, named subfolder (Backups\MyDB). If this is the case, it explains why you aren't seeing backup deletion.

Whether you use an on success condition or an on completion condition is up to you (and your requirements), but if all steps are completing successfully, it's unlikely that your problem is with the transition conditions.

Answer 2

Back again,

I have researched this particular issue regarding why the backups are not deleting, a lot of the other Websites kept talking about "making sure the file extensions are correct" Well they were right. They need to express that point a little clearer however. In other words:

MAKE SURE THE FILE EXTENSION (IE BAK) IS THE SAME ON BOTH PARTS OF THE MAINTENANCE PLANS!

What happened to my customer is that the backup portion of the plan specified “BAK” and the clean up portion specified “.BAK” That little period made all the difference. The clean up worked without any errors, however it was looking for .bak extensions not bak extensions. As you can see in my orginalpost that the clean up subplan command specifies .bak. Dugh.

Thanks all.