30-day misunderstanding of SQL Server-Day28-Misunderstanding of log recovery mode for large-capacity transactions

This series of articles I saw in the sqlskill.com's PAUL blog, many misunderstandings are more typical and representative, the original from the T-SQL Tuesday #11: Misconceptions about... EVERYTHING !!, Hope to help you.

 

Misunderstanding #28: Several misunderstandings about the log recovery mode of large-capacity transactions

 

28 a) Common DML operations can be "minimum logged"

No. In the large-capacity transaction log recovery mode, only a small number of batch Operations Can Be "least recorded logs". The list of such Operations Can Be found in Operations That Can Be Minimally Logged. This is a list suitable for SQL Server 2008. For different SQL Server versions, check the correct list.

 

28 B) using the large transaction log recovery mode will not affect disaster recovery

First, the "minimum record log" operation is performed after the last transaction log backup, and data files are damaged due to disasters. In this mode, tail logs cannot be backed up, therefore, if a disaster occurs, all transactions dependent on the last log Backup will be lost.

In addition, if the large-capacity transaction log recovery mode contains the "minimum record log" operation, this log backup cannot be used to restore to a specific time point. Therefore, this log backup should not be restored at all, or not at all, that is, it can only be restored to the following time points:

• Time Point before log backup starts
• End Time of log backup
• Time Point after log backup is completed

In short, you cannot restore the database to a time point within the log range.

 

28 c) using the large transaction log recovery mode will reduce the log backup size

No.

Although the backup logs are greatly reduced during large transaction log backup, you need to back up all the data partitions (Extent) modified by the large transaction log Backup Based on the bitmap. This means that the large transaction log recovery mode and the full recovery mode have almost the same backup content.