SQL Server Performance

“Remove files older than”

Discussion in 'SQL Server 2005 General DBA Questions' started by jn4u, Feb 13, 2006.

  1. jn4u Member

    Last weekend we upgraded a server to Microsoft SQL Server 2005 x64 Standard. I#%92m working on new backup plan. In the help I find the information about “Remove files older than”, when I run the wizard I can not find the option in the dialog. How do I remove old backup logs and bakup files?
  2. fky New Member

    jn4u,

    Did you find the solution, i get same problem, my maintenance plan does not delete backup files older than ....

    Thanks

    Franck.





    Franck Meunier
    System Admin
  3. joettu New Member

    It looks like you have to add a "Maintenance Cleanup Task" to the plan and that should clean things up.
  4. pgaliardo New Member

    Did anyone get a definitive answer to this? I created a Cleanup Maintenance Plan but it does not seem to delete the actual backup files. I need to manually delete all of the .trn files every couple of days.
  5. satya Moderator

    Are those files are being used by any other application, such as tape backup software, anti-virus check or anti-spyware inclusion?

    If so then DB maint. plan will not delete as the file is being used by another process.
    Also check the history of backup using sp_delete_backuphistory.

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  6. cnikirk Member

    After I went through the maintenance plan wizard I realised I was not deleting old files. At first I tried adding a maintenance cleanup task to my plan. For whatever reason the whole plan would fail that way.

    What you have to do is make a second maintenance task and have it only do the cleanup. This sucks in my opinion.

    I have noticed another problem. If you try making a maintenace plan without the wizard it fails. I don't know why. My plan looked identical to the one I made with the wizard but it wouldn't work.

    I'm not sure if these are bugs or design flaws.
  7. anthobf New Member

    How do you create a maintenance plan with only cleanup tasks? If I do not select a database backup I do not have the option to select delete files older than.
  8. pgaliardo New Member

    From the SQL Management Studio, double click the backup maintenance task you have created. In the lower left window there should be a "toolbox". From there drag a maintenance cleanup task to the main window. Double click the task to set whichever options you like. Then link the cleanup task with the backup task.
  9. anthobf New Member

    Please excuse my ignorance. I am new to sql server. I an using sql server 2000 on windows 2003. I do not know what a Sql Management Studio is. I believe the only tool I have is Enterprise Manager.
    When I use the wizard for Maintenance Plans is there a way to just do cleanup tasks?
  10. cnikirk Member

    This thread is referring to sql server 2005 not sql server 2000.

    In sql server 2000, just make a maintenance plan. Inside that plan is a section that says "remove files older than".
  11. dtipton New Member

    2005 SP1 will add an option to the clean up task to delete files in 1st level subfolders for those users (including me) who select the create sub-directory for each database box in the backup maintenance plan task.

    Failing to include the ability to delete the backup files within the backup maintenance plan task seems like a pretty big mistake by Microsoft. It's basic functionality and was something that was available in SQL 2000. Are people finding other "missing functionality" when working with SQL 2005?
  12. kdefilip Member

    Even with sp1 and the added "first level folder checkbox", we have several SQL2005 installs that have this issue, where the cleanup tasks DO NOT WORK, either as a step in the maintainence job OR as a separate task. The job complets and shows sucessful, but it is far from it. It plane old does not work.
    Hopefully, now that we are done beta testing this product for microsoft, hopefully it will be fixed at some point.
    Meantime, if someone figures out a fix for this issue that does not include a separate VB script to complete, please pass it along.
    Thanks.
    kd
  13. satya Moderator

    SQL 2005 has SP3 now and this problem of remove files older than has been fixed in SP2 (I think) and if your plans are not working as expected you could try what I have done. Once I sense the problem in the plans the only way to solve is to drop the existing plan (keeping the script of it, just in case) then stop & restart SQLAgent with a creation of new maint.plan to take up the charge.
    HTH

Share This Page