Maintenance Cleanup Task(s) running 'successfully' but not deleting back up files

Question

I have a maintenance plan setup for all my user databases on a particular server, which does all the usually things at various intervals.

The 7th and final subplan is a two stage cleanup plan; firstly old full backups are deleted, then old transaction log backups are deleted. These tasks appear to run successfully - the subplan's job history reports that the plan was run on time, and runs xp_delete_file twice - but clearly files are not deleted.

The task settings are:

• Backup Files
• Search folder and delete based on extension - .bak
• Include subfolders
• Delete files older than 2 weeks

and

• Backup Files
• Search folder and delete based on extension - .trn
• Include subfolders
• Delete files older than 5 days

Any ideas?

Solution

And of course, as soon as I post I figure out the answer...

There a number of people suffering similar problems (2005 as well) and solutions range from scripts to SPs and to hotfixes.

Mine was much simpler - I'd specified '.BAK' as the extension, rather than 'BAK'!

It's a bit of a 'Doh!' moments, but nevertheless, I'd have hoped that the GUI would have either handled the situation or at least complained to me about the problem.

Anyway, problem solved...

Comments

[Nick Kavadias]

pls mark yourself as having the correct answer!

[joeqwerty]

Glad you got it figured out, but how and why would the GUI know what you intended when you mistyped the file extension? Should the OS\Software configure things the way it sees fit based on what it thinks you intended or should it configure things based on what you type\set?

[CJM]

I didn't mistype anything - I merely added something extra that isn't required (the '.' divider). If the extension entered starts with a leading period, it can only be extraneous and either a) could be removed or b) a warning given - under Windows, you cannot have an extension that begins with '.'. It's just basic validation.

[Chris W]

The issue these days is the file..bak is a valid filename in windows... but in this case I'd agree that the GUI could warn you to make sure that's what you meant to do - I was caught with this myself a few months back and it drove me nuts until I figured it out!

[CJM]

@Chris W. MyFile..bak is a valid filename - 'MyFile.' is the name, the last'.' is always the separator, and the 'bak' is the extension. So there is no way that my '.bak' extension could be valid, and so there should have been a validation error. But it's a moot point; having done this a trillion times before, I shouldn't have made such a silly error.