What is the use of the transaction log buffer in SQL Server?

Question

I have a question like why do we use in transaction log buffer.

Like SQL Server puts the transaction log in the Transaction log buffer, when the checkpoint runs it transfers the dirty pages(committed) as well as logs to the disk (.mdf/.ldf).

So there must be no active log in the actual transaction log file, all the transactions are waiting for backup process.

Thanks in Advance.

Answer 1

Imagine a modification, for instance INSERT of a row.

SQL Server finds the page(s) that needed to be modified and brings them into memory (if not there already).

Then it constructs log records to reflect the modifications that is about to be performed. These are not written directly to the physical ldf file, for performance reasons. They are cached, in "log buffers".

Then the page(s) are modified, also in memory.

When the transaction is about to commit, SQL Server need to first "harden" the log recods. I.e., physically write the log buffers to the ldf file, before the transation is committed. I.e., a synchonous write, seen from the user's perspective.

The actual data pages will be written at a later time. For instance when checkpoint occurs.