SQL Server Advanced Troubleshooting and Performance Tuning

Chapter 11. Transaction Log

Every database in SQL Server has one transaction log implemented as one or more transaction log files in addition to data files. The transaction log stores information about the changes made in the database and allows SQL Server to recover databases to a transactionally consistent state in case of an unexpected shutdown or crash. Every data modification in the database is stored there, and low transaction log latency is essential for good system performance.

In this chapter, I’ll explain how SQL Server logs transactions and how the transaction log works internally. Next, I’ll cover several best practices for transaction log configuration and talk about how to address “Transaction log full” situations. Finally, I’ll discuss how to troubleshoot insufficient transaction log performance.

Transaction Log Internals

SQL Server uses a transaction log to keep each database in a transactionally consistent state, meaning that data modifications done from within transactions must be either committed or rolled back in full. SQL Server never allows data to be transactionally inconsistent by applying just a subset of the changes from uncommitted transactions.

The transaction log guarantees consistency. It stores the stream of log records generated by data modifications and some internal operations. Every log record has a unique, auto-incrementing Log Sequence Number (LSN) and describes the data change. It includes information about the affected row, the old and new ...