My question is related to SQL Server architecture. Mainly log file. In the picture below it says that when transaction is committed, log record in LOG CACHE will be moved to Log file. Does it mean that Log file contains only committed transactions?
Some sites also state that when COMMIT happens, then dirty page is moved from BUFFER CACHE into LOG CACHE first and afterwards into log file. So I do not understand what happens when COMMIT occurs. I will appreciate any help.
Best Answer
SQL Server uses write-ahead logging so that changes (committed or not) are always written to the log file prior to being written to any data file.
Records written to the transaction log do not necessarily reflect the entire page being modified. Log records typically consist of compensation data that allow transactions to be rolled forward and back.
COMMITflushes the log buffer along with a record of the commit to guarantee durability. See the documentation.In addition to commit, log buffers are flushed when they become full and during checkpoint operations. The log may contain uncommitted transactions and it doesn't matter how long transactions run. During crash recovery, all transactions from the last completed checkpoint are rolled forward from log records and then uncommitted transactions are rolled back. The database is then consistent, containing only committed transactions.
The above was converted from comments made by Dan Guzman