Logs are not as simple as you think.

All those who have been in contact with SQL Server should now know that transaction log is an important part of any SQL Server database. Talking about logs is one of my favorite topics. Some common misunderstandings about logs may be a long article.

In the past two years, I have made numerous conferences named "How simple is simple recovery?" The same report will be made at the DevTeach/SQLTeach conference held in Montreal, Canada on April 9, May this year. Although I cannot describe all the details here, I cannot describe the log organization, why the log is expanded or reduced, and what it means to truncate the log, however, you can basically answer the question "How simple is simple a simple recovery. The simplicity of simple recovery lies in your backup policy. You don't have to worry about how often you back up logs because you cannot back up logs.

Simple recovery does not mean that SQL Server does not need the log function. This is a very common misunderstanding and a general requirement-many users want to run SQL Server in some way without the need to bear the log overhead, so I want to know the special and secret trace flag that allows this ). In fact, there is no so-called operation that does not generate logs, that is, no log operation, although people may need this. SQL Server should at least record enough information about your operations into the log, so that if the system suddenly fails during normal operation, you can cancel or roll back.

SQL Server does not have the possibility of performing log-free operations, but it does have minimal logging. However, I found that the minimum information in the log is not clearly defined. Description of a set of minimum log operations, which indicates that "the minimum log records only recover the information required by the transaction when the recovery is not supported in a timely manner ." This definition means that the minimum log must record enough information to recover or roll back the transaction, even if each individual data row change is not recorded in the log in chronological order. However, other definitions indicate that the minimum log can only roll back the transaction.

Simple recovery is one of the three recovery modes, and the difference between the three modes determines how to manage logs. If you read about the recovery mode in the BOL, it not only lists a series of operations that can minimize the log, but also writes the operations that are only in BULK_LOGGED in a large number of logs) and SIMPLE) the minimum log is used in the recovery mode, while the FULL log is used in the recovery mode. However, I cannot find a complete definition that indicates what "Full Log" means. I used to think that full logs mean that every single row is written to the log, as if you have performed a group of separate INSERT), UPDATE), or DELETE) operations. However, this is not the case for some operations.

When a minimal log operation is executed in the database in full recovery mode, not every individual row is written into the transaction log, but every page modified during the operation. Therefore, if you perform bulk insert operations in the database in full recovery mode, this page is written to the log because each page is filled with new rows, the size of this log record is 8192 bytes, which is the same as that of the SQL Server Page ). If you build or reconstruct an index in the database in full recovery mode, SQL Server does not log every separate index row generated. Instead, once the entire index page is generated, it is recorded in the log.

In terms of space and time, it is much more efficient to log the entire page than to log each individual line. This is only applicable to operations that are considered to be the smallest log operation. Therefore, I think that full logs and individual data modification operations for full logs are not a concept.

Therefore, there is a group of operations that we can call the minimum log operation. Click here: http://msdn.microsoft.com/en-us/library/ms191244 (v = SQL .100). aspx, you can see the complete list of these operations. The special reason for these operations is that they have different log methods in each recovery mode. In full recovery mode, each complete modified page is written to the log. In the batch log recovery mode and simple recovery mode, SQL Server only logs information about the modified page, instead of logging the content on the page. Other operations are real full logs-no matter which recovery mode you are in, they contain all the details of each change line, including transactions containing operations, execution time of operations, and affected pages.

Recovery mode is a great feature added to SQL Server 2000, but it is not easy to use any recovery mode. The more in-depth you understand how SQL Server manages transaction logs, the more wise you can make decisions and determine which recovery mode is suitable for you.