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.
Good catch. I'd forgotten about this, though I'd hope that the task would be smart enough to remind people the period isn't needed.
Thanks for the mention. I hate dealing with Maintenance Plan cleanup wizards so much I created my own ones. The problem with the period just doesn't make sense, and would be such a simple fix to the codebase if it was prioritized.
Thanks guys... you would think there would be some handling, or at least an indication that the period is appended. I'd be satisfied if a text label informing you of this information lay next to the extension field. Hopefully its addressed in the new release ??
:) Smart catch.
Instance Backup needs to be tested per existing DR plan. We at testing-associates provide outsourced software testing services.
I used to have two seperate cleanup jobs (as stated above, one for trn and one for bak) and found you can just use * as the extension. Of course nothing else _should_ be in your backup directory ...
Another point I would add to your list is to always rename the Subplan name from the default "Subplan_1" to something more meanginful. Makes it easier to read in the SQL Agent Jobs section.
I spent quite a bit of time trying to figure this out when I first implemented our first SQL 2005. I just don't understand why they took the simple check box feature to delete backup files within the DB Backup tab in SQL 2000 and cause all of this headache work. In SQL 2000 it built the db backup file names and it already knows what to delete.
One thing we just caught on the cleanup portion...make sure the service account has the right permissions. Would probably not be an issue if the backup folder is configured at the beginning when installing and selecting the service account, but any changes, make sure the account is there. What we did is add the Service account to the SQLServerSQLAgentUser... group added by SQL Server and make that group have read/write on the parent backup folder (if you use sub folders).
Thanks for the tip, had been making myself crazy trying to figure this one out!
The things you are mentioning may or may not fix the problem. I did a lot of Google searches too and tried this on every possible permutation of xp_delete_files. Also, upgraded the service packs and applied hot-fixes on test servers.
The problems with this XP are several. The same code works on some servers but not others. MS includes it in maintenance plans as an undocumented extended procedure, and it will be deprecated in future releases or SQL Server.
I took the advice of a few DBA's out there in the cloud and wrote a PowerShell script that does it flawlessly, irrespective of hardware or SQL Server versions. It takes about a day to figure it out and customize it to your environment (another day to deploy) but really worth the effort.
Ron
