SQL Server Performance

deleting old transaction log backup

Discussion in 'General DBA Questions' started by manojbandha, Apr 21, 2006.

  1. manojbandha New Member

    dear friends

    actually i am taking the transaction log backup og 60 databases everyday at the rate after every half hour.

    now i have to keep last day and the present day transaction log backup.

    can anyone suggest me that how I can delete transaction log back of one whole day automatically by keping one day transaction log backup to be on safe side.

    can anyone suggest please.

    thanks a lot
  2. druer New Member

    I have my log backups done with the BACKUP LOG command (see BOL). One time per day (right after a database backup) I run the command with the INIT option. Thus once I know that everything was successfully backed up, I reinitialize the log file. This way if something crashes during the day I have everything needed to restore to that point in time (last nights full backup, today's log backups.)

    Hope that helps,
    Dalton

    Hope it helps,
    Dalton

    Blessings aren't so much a matter of recieving them as they are a matter of recognizing what you have received.
  3. manojbandha New Member

    thanks
    actually my case is different.

    i want to delete the transaction log backups files if they are more than tow days older.

    please suggest something !!!
  4. druer New Member

    Before doing the INIT, copy the current one off to another server/drive/location. That means yesterday's would still be safe, and then you INIT the file to keep today's.

    Dalton

    Hope it helps,
    Dalton

    Blessings aren't so much a matter of recieving them as they are a matter of recognizing what you have received.
  5. KarenWallace New Member

    If you're using a maintenance plan for the log backups, there's an option for how many days worth to keep, after which SQL Server will automatically delete them (ostensibly -- our production server doesn't often enough that I wrote a VBScript which is run by a SQL Server job that crawls through the backup directory and deletes old files that the maintenance plan should have deleted).
  6. satya Moderator

  7. manojbandha New Member

    Thanks a lot friends....

    actually the recovery model is full in all the databases of all servers.

    I am taking the transaction log backup through stored procedure and not using the
    database maintainance plan.

    please guide me how is can delete the backup files of transaction log with extension TRN older than two days.....

    i need your guidance please.

    thanks
  8. satya Moderator

  9. manojbandha New Member

    Thanks a lot satya...I got the script from the link below:

    http://www.sqlteam.com/item.asp?ItemID=11672 link.

    I am not able to use the link to deleted the backup files of trasanction logs older than two days...

    or I am doing some mistake somewhere...
    i want to tell some details that might help you to give me rightsolution.

    1. i am taking transaction log backup on network share.
    2. for example : \ohusdevbackupserver_namepubs
    3.i want to delete the transaction log backups older than two days .
    4.the format of trans backup file is DBNAME_TRAN_YYYYMMDD_hh_mm_ss.BKP
    5.for example : pubs_TRAN_20060416_00_00_12.BKP

    so please can anyone help to provide the solution that how i can schedule a job to delete the transaction log backup file from the network share older than two days.

    thanks a lot
  10. satya Moderator

    Workaround the script and define your filename pattern at @sql variable and you can schedule the same on SQLAgent to drop the older backups.

    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.

Share This Page