none
Maintenance Plan Cleanup Task not deleting backup files

    Question

  • 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.


    Thanks...Ray

    Monday, March 05, 2012 5:44 PM

Answers

  • 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.

    HTH.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    • Marked as answer by RayBCCUL Tuesday, March 06, 2012 5:10 PM
    Tuesday, March 06, 2012 8:43 AM

All replies

  • open 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:07 PM
  • Task completed. No errors.


    Thanks...Ray

    Monday, March 05, 2012 6:32 PM
  • Hi Ray,

    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.


    Best Regards,
    Peja

    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 Tao Tuesday, March 06, 2012 5:48 AM
    Tuesday, March 06, 2012 5:48 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.

    HTH.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    • Marked as answer by RayBCCUL Tuesday, March 06, 2012 5:10 PM
    Tuesday, March 06, 2012 8:43 AM
  • 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.


    Thanks...Ray

    Tuesday, March 06, 2012 5:11 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
    Thursday, April 26, 2012 2:01 PM
  • Hi Ashwin,

    It solved my problem i am using .bak instead of bak now it is working fine !!!!!!thanks Ashwin for usefull tip!!

    Wednesday, March 13, 2013 10:29 AM
  • where is the "option" in the maintenance plan cleanup task?   I cannot find it.

    系统管理员

    Monday, April 14, 2014 3:48 AM