SQL Server Performance

How to delete backup files older than 2 days in SQ

Discussion in 'SQL Server 2005 General DBA Questions' started by satya.sqldba, Nov 30, 2006.

  1. satya.sqldba New Member



    There used to be an option in SQL server 2000 while creating maintenance plan that allows us to delete files older than 2 days or any particular number of days, but while creating plans in SQL server 2005 , I dont find that option anywhere, how do I delete the files before my disk gets full?

    I would appreciate any help in this area

    Thanks
    Satya
  2. xiebo2010cx Member

    You can find that feature in 'Maintenance Cleanup Task' GUI

    ------------------
    Bug explorer/finder/seeker/locator
    ------------------
  3. satya.sqldba New Member

    It cleans out information from Datebase logs but does not delete the files completely, is there any solution for completely deleting the files?
  4. xiebo2010cx Member

    check carefully

    'Cleanup history' and 'Maintenance Cleanup Task' are different.

    ------------------
    Bug explorer/finder/seeker/locator
    ------------------
  5. satya.sqldba New Member

    Ok Thanks I will try that out
  6. MohammedU New Member

    Still it is not working...
    You can schedule the command using extended proc.
    declare @dt datetime
    -- delete older than two days...
    select @dt = getdate()-2
    EXECUTE master.dbo.xp_delete_file 0,N'D:BackupFolder',N'BAK',@dt



    Mohammed U.
  7. satya.sqldba New Member


    Ya I too have tried using the maintenance cleanup task, but tht does'nt work out.

    Mohammed I will try what u have suggested.

    Thanks
    Satya
  8. satya.sqldba New Member

    I am sorry but that the Maintenance cleanup task worked out, the files are getting deleted, but the problem is I have all the backup files in subfolders in a given foldeer, I know there is an option of include the sub-folders during setting up the task, but I am not able to get that option anywhr while creating a plan with Maintenance cleanup task

    Thanks
    Satya
  9. xiebo2010cx Member

    Hi, Mohammed, is there some extended SPs within sQL Server for copying files with the similar function as windows cmd Xcopy. Thanks in advance.


    ------------------
    Bug explorer/finder/seeker/locator
    ------------------
  10. satya Moderator

    Please note about using undocumented XPs in this case, as they may be deleted or changed in next release of hotfix or service pack, so use them with utmost care.

    Satya SKJ
    Microsoft SQL Server MVP
    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.
  11. Madhivanan Moderator

    satya.sqldba - One more Satya [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  12. xiebo2010cx Member

    Yes, confusing.<img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />------------------<br />Bug explorer/finder/seeker/locator<br />------------------
  13. satya Moderator

    I believe not with the user name & signature [<img src='/community/emoticons/emotion-5.gif' alt=';)' />].<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Contributing Editor & Forums Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>
  14. MohammedU New Member

    I don't think there is any procedure to copy the files but you can make use of XP-cmdshell...

    Mohammed U.
  15. MohammedU New Member

    There are few improvements in SP2 for MPs...
    http://download.microsoft.com/downl...423D-BC8F-B11ECD4195B4/WhatsNewSQL2005SP2.htm

    Maintenance Plans
    Multiple schedules. The Maintenance Plan Designer supports multiple subplans for a maintenance plan. Each of the subplans can have independent schedules for their maintenance tasks.


    Multiple server administration. Maintenance plans will work on multiple servers using master server (MSX), target server (TSX).


    The Maintenance Plan Wizard now includes the highly sought after Maintenance Cleanup task that was in SQL Server 2000. This task removes files remaining from executing a maintenance plan.


    Multiple fixes to individual tasks:


    The Database Backup maintenance plan task now includes the ability to specify the backup expiration option that was previously available in SQL Server 2000.


    The Database Backup maintenance plan task no longer changes the folder location for the backup if it is set to something other than the default location.


    The Backup Database maintenance plan task prohibits the ability to mistakenly set the option to create differential and transaction log backups for system databases.


    The History Cleanup maintenance plan task includes the ability to set the option to delete files using a unit in hours.


    The Update Statistics task includes options for full scan or for sample size as was available in SQL Server 2000 maintenance plans.

    Mohammed U.
  16. catullus Member

    The option to remove files in first-level subfolders is added in SP1.

Share This Page