Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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.
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. COMMIT flushes 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
Log backups back up everything in the log (active and inactive portions) and then mark the inactive portions of the log as reusable. Inactive meaning there's no uncommitted transactions, all the changed data pages have been hardened into the data file, the log records are not needed for replication, CDC or similar.
Uncommitted literally just means open transactions (where there's been a begin tran but no commit or rollback yet). Log records may be written to the log file before the commit, or they may be written as part of the commit, depends what else is going on.
Source here.
log cache, as i understand (correct me if i'm wrong), Uncommitted Trans = Dirty Pages in Buffer Cache, and Buffer Cache got core-relation withLOG CACHE(for ongoing changes) which only written to disk (log file) upon transaction commit. In the case of Long Running Transactions, since the modifications remains inLOG CACHE, any failure happens, i would consider the LOG file doesn't have particular uncommitted transactions.