Maintenance Plan clean up task - not working to plan

A colleague of mine who is getting to grips with SQL Server had an issue after creating and implementing a maintenance plan to manage backup files on his local SQL Server instance.


The problem was with the routine maintenance which should have been removing files with a bak extension that are older than two days. He was trying to emulate a plan from one of our staging servers and despite having entered all the details, albeit manually, the job would execute and apparently succeed but there would be no removal of files. And then this morning his hard drive stopped J


I had a look at the plan that had been created and performed a quick test and the issue was exactly as my colleague had reported it. And then it hit me! The value for File Extension had ‘.bak’, and the task was therefore looking for test..bak as it apparently appends its own '.' to the string.


Removing the extra full-stop (or period) resolved the problem, so the entry simply states 'bak' rather than '.bak'.


Having briefly researched (ok, googled) the issue, I note that others have faced similar problems and most of which were resolved by observing the steps below. Hope it saves you some time…

(As posted on http://www.sqlnewsgroups.net/group/microsoft.public.sqlserver.server/topic24757.aspx by SQL Server MVP Jonathan Kehayias)


- Make sure that you have a '\' at the end of the path, so if the backups are in D:\SQLBackups use D:\SQLBackups\



- Make sure that the extension is bak and not .bak For whatever reason it doesn't like having the . before the bak.



- If you are deleting trn files and bak files, have two separate jobs one to cleanup the bak files and one to clean up the trn files.



- If you backed up databases to separate folders, checking the 'Include first-level subfolders' check box is necessary.