How Simple Is Logging?

As anyone who works with SQL Server should know by now, the transaction log is a vital component of any SQL Server database. Talking about the log is one of my favorite topics, but just to describe the common misunderstandings would take far more space than I have available in this short commentary.

I have a presentation called “What’s Simple About SIMPLE Recovery?” that I’ve done at various conferences over the past two years, and I’ll be presenting it again at DevTeach / SQLTeach in Montreal in May. Although I can’t provide all the details here and describe the log’s organization, why it grows and shrinks, and what exactly it means to TRUNCATE the log, I can give you the bottom line answer to the question in the title of presentation. The thing that’s simple about SIMPLE recovery is your backup strategy. You don’t have to worry about how often to make log backups because you can’t make log backups.

SIMPLE recovery doesn’t mean that SQL Server does no logging. This is a very popular misconception, and a popular request—many users want some way to run SQL Server without having the overhead of logging, and therefore want to know the special secret trace flag that allows that. The truth is there’s no such thing as no logging, or unlogged operations, as much as people might want this behavior. At a minimum, SQL Server needs to log enough information about your activities to undo (or roll back) anything it’s in the middle of doing if a system failure occurs.

Although SQL Server doesn’t have the possibility of performing no logging, there is such a thing as minimal logging. However, I’ve found that the definition of exactly what gets logged in minimal logging isn’t well defined. SQL Server Books Online (BOL) describes a set of minimally logged operations and says “Minimal logging involves logging only the information that is required to recover the transaction without supporting point-in-time recovery.” This definition implies that minimal logging must log enough information to recover, or roll forward, a transaction, even if every individual data row change isn’t logged in chronological sequence. However, other definitions indicate that minimal logging is only enough to roll back a transaction.

SIMPLE recovery is one of three recovery models, and the differences between the three models are all about how the log is managed. When you read the BOL section about recovery models, it not only offers a list of operations that can be minimally logged but also says that those operations are only minimally logged in the BULK_LOGGED and SIMPLE recovery models, and that in FULL recovery the operations are all fully logged. But nowhere can I find a complete definition of what “fully logged” means. I used to think fully logged meant that every single row was written individually to the log, just as if you performed a set of individual INSERT, UPDATE, or DELETE operations. But for certain operations that isn’t true.

When an operation that can be minimally logged is performed in a database in the FULL recovery model, what gets written to the transaction log isn’t every individual row, but every page that’s modified during the operation. So if you perform a BULK INSERT in a database in FULL recovery, as each page gets filled with new rows, the page is written to the log and the log record will be 8192 bytes in size (the same size as SQL Server’s pages). If you build or rebuild an index in a database in FULL recovery, SQL Server doesn’t log each individual index row that’s generated. It logs the complete index pages as soon as they’re generated.

Logging entire pages is actually a lot more efficient in terms of space and time than logging every individual row. But it’s done only for those operations that are considered to be minimally logged operations. So I don’t consider those operations fully logged in the same sense that your individual data modification operations are fully logged.

So there’s a set of operations that we call the minimally logged operations. (For a complete list of these operations, visit msdn.microsoft.com/en-us/library/ms191244(v=SQL.100).aspx.) These opera

As anyone who works with SQL Server should know by now, the transaction log is a vital component of any SQL Server database. Talking about the log is one of my favorite topics, but just to describe the common misunderstandings would take far more space than I have available in this short commentary.

I have a presentation called “What’s Simple About SIMPLE Recovery?” that I’ve done at various conferences over the past two years, and I’ll be presenting it again at DevTeach / SQLTeach in Montreal in May. Although I can’t provide all the details here and describe the log’s organization, why it grows and shrinks, and what exactly it means to TRUNCATE the log, I can give you the bottom line answer to the question in the title of presentation. The thing that’s simple about SIMPLE recovery is your backup strategy. You don’t have to worry about how often to make log backups because you can’t make log backups.

SIMPLE recovery doesn’t mean that SQL Server does no logging. This is a very popular misconception, and a popular request—many users want some way to run SQL Server without having the overhead of logging, and therefore want to know the special secret trace flag that allows that. The truth is there’s no such thing as no logging, or unlogged operations, as much as people might want this behavior. At a minimum, SQL Server needs to log enough information about your activities to undo (or roll back) anything it’s in the middle of doing if a system failure occurs.

Although SQL Server doesn’t have the possibility of performing no logging, there is such a thing as minimal logging. However, I’ve found that the definition of exactly what gets logged in minimal logging isn’t well defined. SQL Server Books Online (BOL) describes a set of minimally logged operations and says “Minimal logging involves logging only the information that is required to recover the transaction without supporting point-in-time recovery.” This definition implies that minimal logging must log enough information to recover, or roll forward, a transaction, even if every individual data row change isn’t logged in chronological sequence. However, other definitions indicate that minimal logging is only enough to roll back a transaction.

SIMPLE recovery is one of three recovery models, and the differences between the three models are all about how the log is managed. When you read the BOL section about recovery models, it not only offers a list of operations that can be minimally logged but also says that those operations are only minimally logged in the BULK_LOGGED and SIMPLE recovery models, and that in FULL recovery the operations are all fully logged. But nowhere can I find a complete definition of what “fully logged” means. I used to think fully logged meant that every single row was written individually to the log, just as if you performed a set of individual INSERT, UPDATE, or DELETE operations. But for certain operations that isn’t true.

When an operation that can be minimally logged is performed in a database in the FULL recovery model, what gets written to the transaction log isn’t every individual row, but every page that’s modified during the operation. So if you perform a BULK INSERT in a database in FULL recovery, as each page gets filled with new rows, the page is written to the log and the log record will be 8192 bytes in size (the same size as SQL Server’s pages). If you build or rebuild an index in a database in FULL recovery, SQL Server doesn’t log each individual index row that’s generated. It logs the complete index pages as soon as they’re generated.

Logging entire pages is actually a lot more efficient in terms of space and time than logging every individual row. But it’s done only for those operations that are considered to be minimally logged operations. So I don’t consider those operations fully logged in the same sense that your individual data modification operations are fully logged.

So there’s a set of operations that we call the minimally logged operations. (For a complete list of these operations, visit msdn.microsoft.com/en-us/library/ms191244(v=SQL.100).aspx.) These operations are special because they’re logged differently in each of the recovery models. In FULL recovery, each entire modified page is written to the log; in the BULK_LOGGED and SIMPLE recovery models, SQL Server logs only information about the pages that are modified, not the page contents themselves. Other operations are truly fully logged no matter which recovery model you’re in and include full details of each row changed, including the transaction the operation is part of, the time it was performed, and the page that was affected.

tions are special because they’re logged differently in each of the recovery models. In FULL recovery, each entire modified page is written to the log; in the BULK_LOGGED and SIMPLE recovery models, SQL Server logs only information about the pages that are modified, not the page contents themselves. Other operations are truly fully logged no matter which recovery model you’re in and include full details of each row changed, including the transaction the operation is part of, the time it was performed, and the page that was affected.