SQL Server 2005 Maintenance Plan not deleting old backups
If you take into account of most Frequently Asked Questions in any SQL community forums, you will get to see problems against database maintenance plans by the users.
Personally I don't like them to use (by default) and professionally always depend (suggest) upon my own scripts to manage the backup & database re-org processes. So coming to the common problem faced by the users is, say X has created a Maintenance Plan to do a nightly full backup and set the backup set to expire after 2 days. Weeks later, old backups are still sitting on the hard drive in the installation directory and X has to manually delete them. So the biggest puzzle is what X is doing wrong?
First option to check on the user side is make sure the *.* file extension is not includes, and also ensure to select correct folder don’t use the “Include first-level subfolders” option. Further as a best option you can create a separate cleanup task within the same plan for each folder tends to work better. Ensure to exclude the backup directories for any Anti-virus or Anti-Spyware scanning as it tends to lock the directories during the scanning, also better to avoid performing tape backups during the same time when the database maintenance schedule is executed.
Further on the maintenance plan options the "Backup set will expire" option just indicates for how many days SQL will be prevented from overwriting the backup set. To achieve the deletion of older backups edit the corresponding maintenance plan, right-click on the maintenance cleanup task and click edit. Then click on the view T-SQL button. This will show you the actual T-SQL which will be executed, you can then past this into a query window and parse it see whether it is executing successfully or not, to track down the problem. Also I would suggest to have another job to drop those redundant old backups, refer to the BlogPost here to drop those redundant backups.
