First - for what is logging needed?
This seems like an easy question - with possibly an easy answer... it's to aid in transaction durability and help in recovery - when the system loses power. Simply put, the transaction log is a way for SQL Server to ensure that a transaction "survives" a power failure. While a transaction is processing, information about that transaction exists within memory. Once that transaction is complete, log rows are written to the log portion of the database on disk. In the event of a power failure - and when SQL Server restarts, SQL Server performs restart recovery (two phases - REDO and then UNDO). Restart recovery happens everytime SQL Server starts; this ensures that completed transactions are persisted into the data portion and that no incomplete transactions end up within the database. For this discussion the specifics about log rows are not important - just that they are enough to "redo" the operations from *just* log information...in the event of a power failure. The information that's needed to recover the log in the event of a power failure - is really just - what's on disk. The data portion is probably out of date (how much so?) and the information kept in the log is used to bring the data up to date. A good question at this point is - how out of date is my data? The answer depends on a background process that runs almost solely to minimize this restart recovery process; it is called CHECKPOINT. A checkpoint occurs to make the data and log more current (but not neccesarily transactionally consistent). What this means is that periodically what is in memory is "synchronized" to disk. Since users do NOT directly read from disk, the data portion of the database (on disk) does not need to be up to the minute. Users accessing data ONLY read from cache - which is current - so only the data in memory needs to be accurate. It is VERY possible that at any given time that not only is your disk out of date but it's not even transactionally consistent. This is NOT a problem. If memory were to be lost (i.e. a power failure) then SQL Server would perform recovery on restart. Restart Recovery runs everytime SQL Server starts. In fact, if you think ONLY about restart recovery needing to bring a database "forward" after a power failure then you could argue that SQL Server would not need information to stay within the log after it's been "synchronized" with the data portion of the database - as long as the transaction(s) had been completed. And - YES - that's true. You can choose to clear the information from the log by changing your recovery model. Where you might have a problem is when you have a more significant failure - such as the loss of a hard drive (and even more interesting - which hard drive: a data drive or a log drive).
Key points:
- The Log is a "write-ahead" log
- The data on disk is NOT guaranteed to be accurate without the transaction information in the log
- The Transaction Log (on disk) ensures transaction durability
- Restart Recovery happens everytime SQL Server starts
OK - so that's it for now... In the next blog entry, I'll tackle "what affects logging."
Thanks for reading,
kt