Transaction Log Backup File Sizes

Question

Transaction Log Backup File Sizes

Mark Buffington

SSC Rookie

Points: 30
More actions
August 14, 2006 at 2:13 pm

#169922

I have 10Gig database that has a transaction log file that varies between 2-4Gig in size (I've seen it balloon up to 10Gig at times). The database is set to bulk-logged. I do a nightly full backup.
My question is, basically, why is the transaction log file so big? What is the algorithm that SQL uses to size this file? If I backup the file up at 9:00 and it's 2gig and backup it up again at 9:01 the 2gig is being backup again when all I'm interested in is the transactional data that makes me recoverable.
Additionally, after a fullbackup and transactional backup how does SQL Server decide what size to keep the transaction log file at? Is it truly empty at this point? I realize there is a cost to always be growing the log file.
Basically, I want a complete and comprehensive understanding of log files.

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

The transaction log file is only truncated by a transaction log backup. When SQL Server truncates the log file (and it may not shrink the actual size of the file), it deletes all transactions that have been committed to the database and were backed up to the transaction log.

You may want to read BOL for more information. I think it does a good job of explaining how SQL Server handles the log files and the different recovery modes available.

hth,

Lynn

Michael Carey-400169 Mr or Mrs. 500 Points: 512 More actions · Answer 2

I have the same question. Why does the transaction log get so big? Books on line just explains "How To", and not "Why"

Sameer Raval Hall of Fame Points: 3214 More actions · Answer 3

Why does the transaction log get so big?

Answer to this question in its simplest form is

Transaction log grows bcz. there are more transactions taking place in DB i.e. Database is busy.

You have to investigate the activities taking place in your DB. May be bulk insert, various job updating the data in various tables on continuous basis etc. It is not necessary to have more inserts taking place [Row increment]. Every small update in table gets logged and anyone of this can be the reason for Transaction log growth.

How to handle fast growing Transaction log is explained in my previous post here.

RE: Unabled to take up backup. Transaction is Full

Posted 12/13/2005 9:44:00 AM

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=24&messageid=243817#bm243902

Hope this helps,

Kindest Regards,

Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 4

When I came to my present job they had 2 database servers they were getting ready to replace. I looked at the servers and noticed that there were several db's with 20+gig tlog files. I looked at the maintenance plans they had setup and they had never taken a tlog backup. Until a transaction log is backed up it is always going to have the completed transactions in it from the last tlog backup (unless your in the simple recovery model). So the log grows based on the transactions that it has in it. Every db has a tlog size it wants to be +/- some percent. That is at the highest transaction load, and your scheduled tlog backups. I take tlog backups on my db's every 2 hours. I have some db's that have <100mg logs and some with >2gig logs. They are always within some percentage of that by there natural growth (if i shrink the 2 gig log, it will grow back to 2 gig through the systems normal use). If you have a highly used system and you backup the logs once a day, I would expect the tlog to be big. If that same tlog were taken every 15min, I would expect it to be considerably smaller than that.

Hope this helps.

Tom

homebrew01 SSC Guru Points: 55548 More actions · Answer 5

Just to add a simplified version .......... When there are updates & writes, SQL logs that information in the transaction log. If you never backup the TLog, then SQL says "I better keep the information in the TLog in case they need it. If they'd back it up one of these days, then I could delete it" ..... You need the TLog information either on a backup or in the TLog itself in case you need to restore your database back to a point in time, such as yesterday afternoon when "such and such" happened. It's up to you how far back you want to save your backed up TLogs ... depends on your business requirements.

Mark Buffington SSC Rookie Points: 30 More actions · Answer 6

Thanks to everyone who replied. Your responses along with additional online reading and some experimentation have given me a better understanding of how Microsoft treats Tlogs.