Delete Old T-log Backup File

  • Hi Guys...

    - I want to delete one week my transaction log backup files so anyone have good script ?

    -Backup file is on network drive when i tried with maintenance plan but its getting failed .

    Any ideas or suggestions?


  • The maintenance plan will handle network locations as long as they are UNC paths and permissions are correct.

    Robert Murphy, Microsoft MCITP (Database Administrator 2008) and MCAD Certified
  • Thanks Robert for a kind reply.

    I am trying with Maintenance Plan and getting error that why looking for better script.

  • What Robert said is accurate. If you're maintenance plan isn't working it's probably a permissions issue. Double check the account that the SQL Server service is running under. It has to be a windows ad account with proper read/write (I believe, you may need full control) access to the network folder/share you are working with.

    I don't know of an easier way to handle this type of request. Check both of the above items and let us know how it works out.

  • HI guys maintenance plan is working and job is getting succeed but files are not deleted .

  • Maintenance plan works fine but old T-log backup file not getting deleted.

    T-log backup file located on network drive in Backup device.

    Any idea?

  • exec master.sys.xp_cmdshell 'PushD "\\YOUR\UNC\PATH" &&("forfiles.exe" /S /M "*.trn" /d -7 /c "cmd /c del @file") & PopD'


  • I would double-check to make sure you configured the maintenance plan to look for the right file location, folder, and you're looking at the right time frame. Did you set it up to delete files older than 1 week but your oldest backup is from a couple days ago, is it looking for bak files instead of the trn extension etc.?

  • maintenance plans for deletes when you backup to a network share are cumbersome.

    just use the script below and it will take the entire share and delete log file backups older than 7 days.

    exec master.sys.xp_cmdshell 'PushD "\\YOUR\UNC\PATH" &&("forfiles.exe" /S /M "*.trn" /d -7 /c "cmd /c del @file") & PopD'


  • i have log files located in backup device so should i have to put backup device name in that path? or it will find automatically .trn file ?

    E.G backup device: Log backup

    File name :Adw Log

    location C:\Temp\Adw Log and it has 3 log files inside the Adw Log

  • /S means it will instructs the forfiles command to search into subdirectories recursively.

  • if you are concerned about what the command will do ( i understand, you don't know me from Adam )

    just make one small change.

    exec master.sys.xp_cmdshell 'PushD "\\YOUR\UNC\PATH" &&("forfiles.exe" /S /M "*.trn" /d -7 /c "cmd /c del @file") & PopD'



    exec master.sys.xp_cmdshell 'PushD "\\YOUR\UNC\PATH" &&("forfiles.exe" /S /M "*.trn" /d -7 /c "cmd /c echo @file") & PopD'


  • Thanks Geoff

    I tried on local machine and it works fine .thanks for nice explanation.

    I can change to the any number?- 7 to 15 or 30?

  • logicinside22 (1/17/2012)

    Thanks Geoff

    I tried on local machine and it works fine .thanks for nice explanation.

    I can change to the any number?- 7 to 15 or 30?

    Yes you can. That is just the number of days.

    Jason...AKA CirqueDeSQLeil
    I have given a name to my pain...MCM SQL Server, MVP
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • thanks Jason for help too..

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply