Monday, March 05, 2012 5:44 PM
I have created a maintenance plan using the wizard in SQL Server 2008 with the following tasks; Check Database Integrity, Reorganize Index, Full Database Backup, Differential Database Backup and Maintenance Cleanup Task. The full backups run monthly and the rest of the tasks run daily including the cleanup task. I've had this plan running for several months and it has been completing successfully. However, last week we ran into some trouble with our SQL server running out of disk space and discovered that the problem was old backup files from this maintenance plam backup left in the backup folder.
The Cleanup task is set to run daily and delete backup files by extension (.bak.) older than 4 weeks and even though it completes without errors it does not delete any files. I did find the post indicating the issue deleting from subfolders but our backup files are not stored in subfolders. Can anyone offer any suggestions on how to resolve this or a way to get more detaled logs form the task to see what it is finding in the folder and why it doesn't delete files that match the criteria?
Thanks in advance.
Monday, March 05, 2012 6:07 PMopen the package in BIDS and select the deleteoldbackupfiles task -->right click only on the task and execute.
let us know the results.
Please mark the post as answered to help others to choose the best. chandra sekhar pathivada | www.calsql.com (SQL Server Community Website)
Monday, March 05, 2012 6:32 PM
Task completed. No errors.
Tuesday, March 06, 2012 5:48 AMModerator
I would like to suggest you to use process monitor to monitor the Cleanup task process and check whether it was pointed to the subfolders to delete BAK files.
One more thing, please check and post the security right of the folder which stores the BAK files.
Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
- Edited by Peja TaoModerator Tuesday, March 06, 2012 5:48 AM
Tuesday, March 06, 2012 8:43 AM
In the maintenance plan cleanup task for the option "File extension" use "bak" instead of ".bak" and then try.
The . (dot) infornt of the bak will cause it not to cleanup any files but it will succeed without issues.
Regards, Ashwin Menon My Blog - http:\\sqllearnings.com
- Marked As Answer by RayBCCUL Tuesday, March 06, 2012 5:10 PM
Tuesday, March 06, 2012 5:11 PM
Thanks for the suggestions. The ".bak" was the problem. When I ran process monitor it showed that the process querying *..bak and NO SUCH FILE.
Thursday, April 26, 2012 2:01 PM
We had a similar issue today without our databases. We were getting SCOM alerts that the plan failed. Upon further investigation it appears that you actually are supposed to have 2 maintenance plans.
- 1 for bak files to be cleaned up
- 1 for the trn files to be cleaned up
You can right click and copy the existing plan then move it above or below the current plan and edit it to clean up the other file names.
- Proposed As Answer by BrownT4305 Thursday, April 26, 2012 2:02 PM
Wednesday, March 13, 2013 10:29 AM
It solved my problem i am using .bak instead of bak now it is working fine !!!!!!thanks Ashwin for usefull tip!!