• 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.

    HTH,

    Rich