Transaction log is a very important component in SQL Server to keep transaction durable even in an event of system failure. It’s generated while a modification taking place in most of data pages in the memory that eventually being written into data files within a database. Inserting, deleting and updating DML operations will generate transaction logs. DDL operations, such as alter table, create procedure, which cause changes to the system tables, will generate transaction logs as well. Transaction logs are generated by transactions. Any data modification to persistent data in the data files will be at least implicitly involved in a transaction. As far as I know, table variables are not transactional even though SQL Server actually writes the data in table variable into the data files of tempdb. (this is an reason why table variable is faster)

Let’s say, before a transaction being finished (commit or rollback), the system had a power failure. what would happen when the system gets restarted? SQL Server will rollback this transaction based on the transaction logs. What if the transaction has been committed but the modified data page has not been flushed to the disk before a power failure? SQL Server will roll forward the transaction and make up the data page. How is the durability feature being maintained for a transaction? — replay-able transaction logs.

While a record is being modified,

• An exclusive lock will be applied to that record.
• The data page in the memory gets modified.
• Log records will be saved in the log buffer.
• While committing, the log buffer is forced to be saved in the transaction log
• After previous step done, locks are release
• Client application gets acknowledged in which the transaction is done.

Log buffer is a small contiguous memory structure, only 60k maximum, to host in memory transaction logs per database. When the transaction is committed, entire buffer will be written to transaction log file before commit finishes. While running a big transaction, the log buffer will be filled up immediately. As this happens, the buffer will be forced to be written to the disk and being cleaned up for rest of the transactions. In concurrent transaction within one database scenario, the log buffer contains mixed logs from all active transactions. Any one of the transaction gets committed or the 60k buffer gets full will cause entire buffer to be written.

SQL Server using write ahead mechanism to guarantee the logs to be written ahead of flushing data pages. So checkpoint process is also the process causing logs to be flushed because this process writes all the dirty pages to the disk!

Log writer is the process to flush the log buffer to disk. Every instance of SQL Server has only one log writer. This process looks after all the log buffers for every database. It calls asynchronous IO to complete the IO operation. While committing a transaction, it will block the user process until the completion of asynchronous IO. While user process is blocked, log writer can still work for other database log buffers. In other cases, the log writer is working asynchronously with user sessions.