TLOGbackup file larger than Full backup

[Jimmy2128]

I have noticed that my TLOG Backup file is larger than the FullBackup

Example:
Fullbackup: 2,182,684 kb
TLOGback.BAK: 33,661,280 kb

Can some please explain to me how can I fix it and why the log file is larger than the full back up.

Thanks

James

 

[mrdenny]

How large is your transaction log? Are you ever deleting the TLOGback.BAK file?

My guess would be that you have every transaction log backup you've even done in that one file. Check the file with the restore headeronly command.

RESTORE HEADERONLY from disk='d:\TLOGBack.bak'

There will be one record returned for each backup in the file.

Another option is that you have LOTS of update commands happening on the system. For each update command the log will grow even through the database doesn't grow. In fact for each delete the log will grow even though the amount of data shrinks.

As an example if you insert a record, then update it 10 times, then delete it you will have 12 entries in the transaction log, but a net growth of 0 records in the database.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com