• I've run into something similar on a regular basis with my servers. Mine occurs on the full .bak as well as the .trn. I'm assuming SQL2K on Win2K(3).

    If you have the the "Run integrity checks before backups" checked on in the maint plans, and the integrity checks fails -- usually because someone is in the database and it can't take it to single user mode -- it won't delete the backup files. You probably don't have this problem when you run the full backups, because that is at midnight. But when you are doing tran logs during the day it won't work and you see the tran logs build up.

    The catch is that you have to completely rebuild all the the maint plans from the ground up. For some reason, if you just go in and uncheck the box it will still run the integrity checks before backups.

    I built the job below to blow out all backups (trn and bak) between 2 and 7 days that are in the msdb log. I run it once a day and on error out of disk space. You should be able to modify it easily enough to just go after tran logs.

    declare @file_name varchar(100)
    
    declare FileList cursor for
        select  physical_device_name
        from msdb.dbo.BackupMediaFamily bmf 
        left join msdb.dbo.BackupSet bs on 
           bs.media_set_id = bmf.media_set_id
        WHERE datediff(hh,bs.backup_start_date,getdate()) > 48 
        and datediff(hh,bs.backup_start_date,getdate()) < 168 
        and physical_device_name not like '%BEXVDI%'
        ORDER BY bs.backup_start_date desc
    
    OPEN FileList
    FETCH NEXT FROM FileList INTO @file_name
     WHILE (@@fetch_status  -1) 
      BEGIN
        --print @file_name
        SELECT @file_name = 'DEL ' + @file_name
        EXEC XP_CMDSHELL @file_name
        FETCH NEXT FROM FileList INTO @file_name
      END
    DEALLOCATE FileList

    Jus my $0.02.



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.