June 19, 2015 at 5:18 am
Have a SQL 2014 install and cannot for the life of me get the maintenance plan to remove old backups. I've tried everything. Rights to the folder where the backups are stored are adequate, extension set in the clean up task is as it should be, etc. Log shows the job ran successfully. Running the command manually shows successful completion, but backups are still not removed. I'm at a loss.
June 19, 2015 at 7:14 am
How did you set the job up and what are the specifications of the backups? If the files do not meet the properties you are setting in the mx plan it will succeed as they will not qualify for deleting.
Can you send screenshots of the mx plan job and a window of the backups you want deleted so others may verify what has been done?
Joie Andrew
"Since 1982"
June 19, 2015 at 9:49 am
When I get access to the machine again I can get screenshots, but to give you an idea, I set up maintenance plans such as this pretty regularly (at least 3 times a week) in conjunction with an application we sell and each one of them is done exactly the same. Nothing different about this particular one. The backups are saved to a C:\Data\SQLBackup folder as .bak files. The clean up task is set to look at this folder for bak files (no dot preceding the extension). I've tried setting the path as C:\Data\SQLBackup and C:\Data\SQLBackup\ with the same results. I've tried running the job as the domain admin and have given domain users/admins full control of C:\Data\SQLBackup. Nothing.
I've gone though the maintenance plan more than a few times and have recreated it twice. Have had others look at it to see if there was something I'm missing and it looks fine to them also.
June 19, 2015 at 11:57 am
What is the retention time set on mx plan?
How old are the backups that you were expecting to be deleted?
Joie Andrew
"Since 1982"
June 19, 2015 at 2:19 pm
Retention time is 1 day and the existing backups are recent. I've tried deleting all the backups in the folder, letting it do it's thing for a week and come back and there are a week's backups in there.
June 19, 2015 at 2:41 pm
I had this problem a while back and it was aggravating the heck out of me... in the end, it turned out to be pretty simple and stupid: In my delete step of the maintenance plan, they'd entered the file extension as .bak instead of just bak
Apparently, it doesn't like that.
June 19, 2015 at 2:50 pm
cphite (6/19/2015)
I had this problem a while back and it was aggravating the heck out of me... in the end, it turned out to be pretty simple and stupid: In my delete step of the maintenance plan, they'd entered the file extension as .bak instead of just bakApparently, it doesn't like that.
That has already been ruled out
June 19, 2015 at 3:14 pm
If, for any reason, the maintenance plan cannot delete a backup file that it should (file locked due to file system backup for instance) the file will not be deleted by a subsequent run of the maintenance plan. At least that has been my experience in the past and one of the reasons I use a roll your own T-SQL based set of procedures.
You will need to delete the older backups manually.
June 19, 2015 at 3:18 pm
Deleting manually is fine for older ones, but these are ones it should be deleting on its own. In other words it should never have more than 1 day in there, but it never deletes any of them.
June 21, 2015 at 6:02 pm
Try opening the maintenance plan in BIDS and run it manually... disable the other steps if you prefer, but see if it'll perform the delete. If it can't, you should at least see something in the execution log.
Also, have you tried deleting a specific file via T-SQL under the same account as you're running the job? Something like xp_cmdshell 'del C:\Data\backup.bak' Just to see what message you get, if any?
You've ruled out the path and extension, and it sounds like you've ruled out the expiration time... the only things left are folder security and a file lock.
June 22, 2015 at 6:53 am
cphite (6/21/2015)
Try opening the maintenance plan in BIDS and run it manually... disable the other steps if you prefer, but see if it'll perform the delete. If it can't, you should at least see something in the execution log.Also, have you tried deleting a specific file via T-SQL under the same account as you're running the job? Something like xp_cmdshell 'del C:\Data\backup.bak' Just to see what message you get, if any?
You've ruled out the path and extension, and it sounds like you've ruled out the expiration time... the only things left are folder security and a file lock.
Have done all of this and nada. Everything goes as it should, yet nothing deletes.
June 23, 2015 at 6:13 am
I've struggled in the past with this, too, and I think it has to do with making the Backups with the "Expiration" set correctly. Check out this link, and see if it helps:
June 23, 2015 at 6:22 am
I was always under the impression that expiration only affects when a backup can be overwritten, which is separate from the clean up task.
June 23, 2015 at 6:26 am
I believe they are interrelated, and in order for the Cleanup task to work, the Backup must have expired... at least that's my experience with it. Do you have your Backup task setting the Expiration?
June 23, 2015 at 8:05 am
I had encountered the same thing. It turned out that more disk space solved the problem. Perhaps the system will not delete the previous backup unless it is sure that the current backup is successful?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply