What is a minimized log (Minimal Logging)? When the recovery model for a database is simple or bulk_logged, for operations that minimize the log type, the transaction log does not log individual rows of data, but instead records the modification log for the corresponding page and district structure. This significantly reduces the number of transaction logs generated by the operation. For example, inserting 200 rows of data into a page of data, in the case of minimizing logging, only logs a change to this data page, not 200 insert logs.
actions to minimize log types
- SELECT into
- BULK derivative operations, including BULK insert and bcp
- INSERT into ... SELECT, which consists of two cases:
a)Use OPENROWSET (BULK ...) in select
b)The target table does not have a nonclustered index and inserts it into the
over 8 pagesof data, and when TABLOCK is used. If the target table is empty, you can have a clustered index, or not if it is not empty.
- Partially update a column of a large value type
- When you use. Write to insert data or append data in update
- Use WRITETEXT and UPDATETEXT for LOB fields to insert or append new data, excluding updates.
- Index operations, including create Index,alter index REBUILD,DBCC dbreindex,drop Index on tables/views (regeneration of new heap will be minimally logged)
minimizing logging in data importThis article focuses on minimizing logging of data imports, which refers to the bulk insert derivative operation. Many theories are common to other types of operations.
1. Normal InsertLock and log records are used in SQL Server to guarantee the ACID properties of database transactions. During the entire transaction of inserting a row of data, the row is locked to avoid concurrent transaction access, and the line is also written to log records. Insert a row of data
probablyThe steps are as follows:
- Locks a row with a row lock.
- Write log records. The log record contains the full data of the inserted row.
- Data rows are written to the data page.
When MultiRow is inserted, each row repeats the above steps. This refers to the approximate operation of the prototype, the actual processing of more complex, such as lock escalation, constraint checking and so on
2. Bulk ImportWhen bulk imports a commit transaction, all data pages used by the transaction are written to disk, which guarantees transactional atomicity. Equivalent to doing a checkpoint every time a transaction is committed. If a bulk transaction needs to be rolled back, SQL Server retrieves the page or zone information involved in the transaction and then marks it as unused. When you back up the transaction log, the data pages and index pages that are involved in bulk are backed up to a log backup. When you restore a log backup that contains bulk transactions,
not supportedRestores to a specified point in time. The eighth page of each data file is the BCM page (BULK chandged Map), followed by a BCM page every 511230 pages. Each bit on the BCM represents a region, and if this bit is 1, the area has been modified by the bulk type operation since the last backup log. After the next log backup, these modified extents are copied to the log backup.
3. Conditions to be met when importing data using minimal loggingIt is not possible to implement the minimum log derivative in any case, and the judging logic is as follows (from Itzik Ben-gan)
a)Version judgment logic prior to SQL Server 2008:
non-full Recovery model and not replicated
and TABLOCK
and (Heap
OR (B-tree and empty))
b)SQL Server 2008 and later versions of the Judgment logic:
non-full recovery Model
and not replicated
and ( (heap and tablock) & nbsp or (b-tree and empty and tablock) OR (b-tree and & nbsp;empty and tf-610) OR (b-tree and nonempty and TF-610 and key-range)
Starting with SQL 2008, you can use trace flag 610 and an exclusive key-range lock to minimize log operations for empty/non-empty clustered index tables. Exclusive Key Range Lock Function Example: Clustered index Table TB (ID INT), there are currently 4 rows of data, respectively, 1,1000,2000,3000. Now you need to insert 500 rows of data into the table, which has a value range of [1001,1500]. When inserting, SQL Server does not need to obtain an exclusive lock for the clustered index as a whole (like tablock), but only gets the exclusive key range lock of the original key value interval. This is to get X Key-range LOCK on the (1000,2000) interval. Data that is not in this interval can still be accessed by other processes. If you want to minimize the logging derivative of a non-null index table, you need to pre-sort the import data by the index key column of the target table and enable trace flag 610. As can be seen from the above-mentioned logic, the prerequisite for implementing minimal logging is: the database
is not a full recovery model and the table is not marked for replication。 You always need to use TABLOCK for heap tables. For the index table, it is divided into empty table and non-empty table two cases to handle. This part of the text in the following example of the expansion to illustrate.
observing bulk imported logsUse an Sys.fn_dblog system function to find the relevant log content. Fn_dblog accepts two parameters to specify the log interval to query, representing the start and end LSN, respectively. In the Output field, this article needs to be concerned with operation, context, Log Record length and allocunitname. Because it is an undisclosed function, the meaning of the content represented by the output needs to be interpreted in the light of personal experience and the "consensus" of all.
- Operation (LOP): Indicates what log operations are performed, such as modifying the behavior Lop_modify_row, setting the bitmap page to Lop_set_bits, and so on.
- Contexts (LCX): The context of a log operation, which generally represents the type of object being affected. such as LCX_GAM,LCX_HEAP,LCX_PFS and so on.
- Log Record length: journal lengths in bytes
- Allocunitname: Indicates the specific object being affected
The script is parsed using the following script from Jakub K
--Log Bar directory data and total sizeSELECT COUNT(*) asNumRecords,CAST((COALESCE(SUM([Log Record LENGTH]),0)) / 1024x768./ 1024x768. asNUMERIC ( A,2)) asSIZE_MB fromSys.fn_dblog (NULL,NULL) asDWHEREAllocunitname= 'Dbo.tablename' ORAllocunitname like 'dbo.tablename.%'; --average length and number of logs of each typeSELECToperation, Context,AVG([Log Record LENGTH]) asAvglen,COUNT(*) asCnt fromSys.fn_dblog (NULL,NULL) asDWHEREAllocunitname= 'DBO.DESTINATION_PK' ORAllocunitname like 'dbo.destination_pk.%'GROUP byOperation, Context,ROUND([Log Record LENGTH],-2)ORDER byAvglen, operation, Context;
Minimizing log records in derivatives: Background and theory
