Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2004
    Posts
    4

    Unanswered: Why won't my transaction log backups purge

    Hi

    We are running SQL Server 2000 and making full database backups using a maintenance plan. The transaction logs are being backed up via a separate plan.

    However, the transaction log backups aren't purging but the database ones are. They are both run under the same id and both write to the same directory. Old text reports also get purged fine. I've got 'Delete older than 1 week' ticked for everything.

    Can anyone think of anything else to check ?
    Thanks

  2. #2
    Join Date
    Aug 2002
    Location
    Prague
    Posts
    77
    how do you backup logs? Every log into separate backup device or all into one device? mojza

  3. #3
    Join Date
    Sep 2004
    Posts
    4
    Hi,

    They are the logs from the user databases and a sub directory is created for each database.

    Cheers

  4. #4
    Join Date
    May 2003
    Location
    Rochester, NY
    Posts
    135

    Unhappy

    ...one thing I've noticed is that files will sometimes not delete if the maintenance plan has been recently replaced. The backups created after the change roll off correctly because the PlanId is the same, but older backups do not because the PlanId is now different...(at least that is what I am thinking...)

  5. #5
    Join Date
    Aug 2002
    Location
    Prague
    Posts
    77
    And the job that performs transaction log backup is reported as successful? what is the contents of report file? There should be something like

    [2] Database pubs: Delete Old Backup Files...
    1 file(s) deleted.

    and at the end:

    End of maintenance plan 'DB Maintenance Plan1' on 6.10.2004 17:30:01
    SQLMAINT.EXE Process Exit Code: 0 (Success)

    Any errors?

  6. #6
    Join Date
    Sep 2004
    Posts
    4
    Hi

    There is no reference to deleting transaction log backups, but the exit code is actually 1 (failed) I've pasted the end of last night's below.
    I've also checked the errorlog as well and that only has a line to say log backed up.
    Is there anywhere else to check for errors ?

    Cheers
    Fay


    Errorlog
    -------
    2004-10-12 00:00:18.94 backup Log backed up: Database: Watchdog, creation date(time): 2004/03/29(13:35:27), first LSN: 755:14678:1, last LSN: 757:10265:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Watchdog\Watchdog_tlog_2004101 20000.TRN'}).

    logfile
    ------
    [25] Database Watchdog: Transaction Log Backup...
    Destination: [D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Watchdog\Watchdog_tlog_2004101 20000.TRN]

    ** Execution Time: 0 hrs, 0 mins, 1 secs **

    [26] Database Watchdog: Verifying Backup...

    ** Execution Time: 0 hrs, 0 mins, 2 secs **

    Deleting old text reports... 0 file(s) deleted.

    End of maintenance plan 'Daily Topaz TX Log backup' on 12/10/2004 00:00:20
    SQLMAINT.EXE Process Exit Code: 1 (Failed)

  7. #7
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Just for grins ... check the properties of the tran log files and see if any of them have been set to read only. If any one of the files that should be deleted cannot, then none will be after the one that is the show stopper. Also, have you tried to manually delete the files one by one that should have been deleted by the maint plan?

  8. #8
    Join Date
    Aug 2002
    Location
    Prague
    Posts
    77
    Another thought - i had a similar problem 2 years ago and the cause was legato networker-backup software which failed to finish correctly and held locks on backup files to delete. Don't you use something like that in your environment. mojza

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •