Post reply

DB Maint Plan not automatically clearning TRN''s

  • September 29, 2006 at 3:38 am

    #61812

    I have a couple of servers all on SQL 2000 SP4, one a cluster, one not. Within the maint plan I specify that the Transaction Logs be backed up too. However I also specify that anything older than 1 day be removed (as I do for backing up the database). On both the servers, the maint plan does the correct thing for the databases (i.e. removes those older than 1 day) but fails to do it for the TRN's allowinig them to build up forever!!! Any ideas?

  • October 2, 2006 at 8:00 am

    This was removed by the editor as SPAM

  • October 3, 2006 at 10:21 am

    #663896

    Did you specify Remove Files Older Then .... on the Transaction log Backup tab of maintenance Plan too?

    Regards,Yelena Varsha

  • October 9, 2006 at 4:05 am

    #664756

    Yes I did.

  • October 9, 2006 at 9:07 am

    #664831

    Nad,

    check if the account that is used to run the job has Delete right on the folder with the transaction log backups

    Regards,Yelena Varsha

  • October 9, 2006 at 9:54 am

    #664838

    check that the maintenance plan is actually completing and not failing on the last (or only) step.  It may be that the backups are there; but the plan is failing and that is why the logs are not deleted.  This was happening at my client site when I got here two weeks ago - and it had been happening for over a month.  There were over 30,000 transaction logs to be manually deleted.

    Thank-you,
    David Russell
    Any Cloud, Any Database, Oracle since 1982

  • October 9, 2006 at 3:17 pm

    #664874

    Also, how are you doing the backups? Are the Full backups appending to the same file? Are the transaction logs appending to the same file?

    I'm guessing that the full bu's don't append and the tlogs do append. To 'expire' a file, SQL Server uses the NT file date/time. If the file is appended to, then the date/time is changed.

    If you are using the maint plan to remove files, then all backups must be made to separate file names.

    -SQLBill

  • October 13, 2006 at 3:57 am

    #665665

    It's a bug!

     

    http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303292

     

    This must have crept in with one of the Service Pack's because I know for a fact a standard Install does clear TRN's. The resolution is crap IMO.

Viewing 8 posts - 1 through 7 (of 7 total)

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