SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Transaction Log Growth, do you need it?

Transaction Log Growth, do you need it?

Steve Jones
Steve Jones
SSC Guru
SSC Guru (327K reputation)SSC Guru (327K reputation)SSC Guru (327K reputation)SSC Guru (327K reputation)SSC Guru (327K reputation)SSC Guru (327K reputation)SSC Guru (327K reputation)SSC Guru (327K reputation)

Group: Administrators
Points: 327260 Visits: 20085
The "log" is a misnomer, IMHO. It's more of a change tracking file for the db than a log.

You shouldn't, in general, read the log. Especially now with auditing built into the product. Really the log is there to ensure that changes made to data are completely written to disk and committed in case power goes out, something corrupts, etc. Changes are written to the log, then once that is hard on disk, the changes are made to the data file.

In terms of recovery, you use the log backup file to do a log restore. Typically if I backed up at 1:00am once a day, and then did a log backup every hour, when I did a restore, I'd restore:
- the full backup (data as of 1:00am)
- log backups, in order, starting at 2:00am (possible 1:00am) and continuing through failure.

If I crashed at 8:02am, I'd have one full backup and then 7 log backups to restore. If you look up the RESTORE LOG command, you'll find syntax.

In terms of your system, I think that you are not running log backups, which you need to do. You should schedule those when you set things up, and if you want an easy way to do it, use maintenance plans. You can delete log files older than a couple days if space is an issue. This will allow all the "records" in the log file to be marked as backed up and then that space is reused. Your log file will need to be the size of all the changes to data that occur in between log backups. If you run log backups hourly, you'll get an idea of how many changes are made to data in an hour.

Does this make sense?

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Rich Mechaber
Rich Mechaber
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5709 Visits: 3691
To add to Steve's good words:

Forget trying to read the log. You need to understand (1) why it's needed (2) how it works and (3) how to use it.

1. Why a t-log? Because there's no way to maintain a transactionally consistent database without it. That means either all or nothing in a "transaction" - loosely, a batch of commands consisting of INSERTs, UPDATEs, DELETEs, etc. - gets written to the d.b. Think of having "half" an engine rebuild on your car: can you drive now? Nope, you're screwed. You either put off the rebuild, or you bring it in and get the entire job finished. Only then can you drive the car. The analogy continues: say you get half-way through the engine rebuild and you realize you made a mistake removing vacuum hoses early on and you're on step 124 and you want to go back to the beginning and start over. This is like a ROLLBACK. If only car maintenance were so easy :-)

2. How's it work? Read what others already posted on this thread. Changes you make to your data first get written to the transaction log, then later the updated data gets flushed from RAM to disk. Why does it get so big? If I INSERT a million rows into an otherwise empty d.b. and then issue a DELETE for those rows, how much data is in my d.b.? Nothing, right. But my t-log has 2 million transactions-worth stored in it. This is why, left unchecked, your transaction log can swell in size and why so many people make the misguided effort to make it smaller. "Misguided" b/c there is a correct way to keep your transaction log from growing ever larger, and that means....

3. If you're in FULL recovery mode (view this in SSMS by right-clicking your db, Properties, Options), then you need to manage your transaction logs. Read up on this. The basic idea is that you have 2 kinds of backups: full database backups (we'll leave differentials out of this) done, say, daily, and transaction log backups done, say, hourly. When you back up the transaction log, inactive portions of the log file are marked as re-usable for recording future transactions. So, if you're in FULL recovery mode, you must back up the transaction log. (Here's a nice link: http://sqlskills.com/BLOGS/KIMBERLY/post/Database-Maintenance-Best-Practices-Part-III-Transaction-Log-Maintenance.aspx. )

Think of six white boards (your log file), and you're the teacher. You write stuff on the boards as you lecture, and your students follow along. You get to the end of your sixth white board, what do you do? "Oh, I don't need that stuff on boards 1 and 3 anymore - everyone's written that stuff down in their notes already." So, you erase boards 1 and 3, and you keep on writing as you talk.

The bit about students taking notes on boards 1 and 3, and you wiping them out? That's the log backup. You don't need the stuff on the boards anymore, because that info. has been backed up. Those two boards are free to re-use. But - and here's the part to not forget - how much "log" space do you have now in total? 6 white boards' worth, that's how much. You erased the ink on the boards, you didn't pull the boards of the wall and toss them in the dumpster, did you? Your log file hasn't changed in size a bit. You just cleared inactive data off two boards so you could re-use them.

So you keep teaching. You re-fill boards 1 and 3, but now they're all full again. This time, you've decided you need to KEEP all of the 6 white boards b/c they all contain vital, current information. You have a full transaction log. By default, SQL server will now grow the transaction log. So, you call maintenance, and they come and install 3 more white boards in your classroom. Voila! More space to write!! Great, huh? Well, maybe.... you lost a lot of class instruction time waiting for maintenance didn't you? That's the performance hit SQL takes when it has to grow the log file bigger, and that's why folks here have recommended leaving the log file as big as it needs to be and NOT shrinking it. You wouldn't remove 5 of your 6 white boards from your classroom at the start of school when you KNOW you'll likely need 6 boards at some point during the semester, would you?

Oh, and what if maintenance comes and says, "sorry, we can't fit any more white boards on these walls, there's no more room"? That's like your transaction log growing so big that your disk is full. No more data can be INSERTed! Class comes to a halt.

School's out.



You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum