Post reply

Delete Old T-log Backup File

  • January 17, 2012 at 8:53 am

    #262834

    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?

    Thanks

  • January 17, 2012 at 9:06 am

    #1434673

    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

  • January 17, 2012 at 9:11 am

    #1434677

    Thanks Robert for a kind reply.

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

  • January 17, 2012 at 9:33 am

    #1434695

    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.

  • January 17, 2012 at 10:35 am

    #1434740

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

  • January 17, 2012 at 1:52 pm

    #1434853

    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?

  • January 17, 2012 at 1:56 pm

    #1434857

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

    GO

  • January 17, 2012 at 2:02 pm

    #1434864

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

  • January 17, 2012 at 2:04 pm

    #1434869

    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'

    GO

  • January 17, 2012 at 2:32 pm

    #1434885

    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

  • January 17, 2012 at 2:35 pm

    #1434889

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

  • January 17, 2012 at 2:47 pm

    #1434895

    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'

    GO

    to

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

    GO

  • January 17, 2012 at 2:57 pm

    #1434897

    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?

  • January 17, 2012 at 3:00 pm

    #1434898

    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
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • January 17, 2012 at 3:19 pm

    #1434905

    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