There is a requirement in relational database systems that transactions must be durable. This is “D” in the ACID properties of transactions. The system must ensure that if a sudden crash happens, the transaction can be replayed. SQL Server fulfills this requirement by capturing all transactions in a physical file called a transaction log file.

In essence, every time a transaction is committed, SQL Server records changes produced by that transaction in a transaction log. Even if the transaction has not been persisted in the data file, it is available in the transaction log and can be replayed in the event of a sudden crash.

Recovery Models and Transaction Logs

SQL Server operates under three recovery models – Full, Bulk Logged, and Simple.

Under the Full recovery mode, ALL transactions are logged. Thus, the database can be fully recovered if a crash happens. This also means that the database backup can be restored to a specific point in time if the transaction or the related backup is available. Under Full and Bulk-Logged Recovery modes, transaction logs are truncated whenever there is a log backup executed.

Under the Simple Recovery mode, ALL transactions are still logged. However, the transaction log is truncated every time the database executes the checkpoint.

A checkpoint happens when SQL Server writes dirty buffers to the data file. Dirty buffers are essential pages stored in memory that have been changed by transactions, such as that the state in memory does not match the state in the disk. However, we won’t discuss this here. In the Simple Recovery mode, SQL Server captures all these changes in the Transaction Log to keep them until they are persisted.

#sql server #transaction log #sql