transaction log not erasing files but full backup does

Question

transaction log not erasing files but full backup does

iajm

SSC Journeyman

Points: 85
More actions
January 29, 2004 at 10:40 am

#152028

OK here goes. I go to EM set up database maintenance plan so SQL can back up the transaction log every hour and a full backup once a day ereasing any transacton log that is more than 24 hours old. I back up the DB 1 time a day and erease the previous days DB. The transaction logs are 120MEG and the DB is 15 gig. They are both backing up but only the backupfiles ( the regular backup files) are being ereased and transaction log is not being ereased after 24 hours. I have it set up so I am going from SQL server A ( \\a) to backup file on a different server (\\b\backup). Again only the DB backup files are being ereased after 24 hours. Both the files have the same destination folder and are backup to Tape nightly.

Ian

Viewing 4 posts - 1 through 3 (of 3 total)

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

Vik Nat Hall of Fame Points: 3930 More actions · Answer 1

Have you made changes the the Maint Plan after setting it up, that could cause it not to delete the old .trn? Also if you execute the job through SQL jobs, say manually , it will complete the task but will not erase the old .Bak file or .trn file.

SQLBill SSC Guru Points: 51440 More actions · Answer 2

You are probably appending the Transaction Log (TL) backups to the backup file. Correct?

If so, here's what's happening:

1st TL Backup @ 0100 => backup file time of 0100

2nd TL Backup @ 0200 => backup file time is now 0200

3rd TL Backup @ 0300 => backup file time is now 0300

See what happens, SQL Server can't delete a file that's 24 hours old, because there isn't a file 24 hours old, it's only 1 hour old. SQL Server also can not delete PART of a file, it must delete the whole thing.

-SQLBill

iajm SSC Journeyman Points: 85 More actions · Answer 3

SQLBill,

1st TL Backup @ 0100 => backup file time of 0100

2nd TL Backup @ 0200 => backup file time is now 0200

3rd TL Backup @ 0300 => backup file time is now 0300

I have 10 TL backups per day. There is no way through Database Maintence Plans to append the backup file. But I see what you are talking about... if you right click on your database -->AllTask--> Backup database you can get that option.