SQL Server Transaction Log Truncate && Shrink

Catalogue

What is a transaction log

Composition of the transaction log

Transaction log Size Maintenance methods

Truncate

Shrink

Index fragmentation

Summarize

What is a transaction log  Transaction Log is a record of a series of actions performed on a database management system and uses these records to ensure acid availability in the event of a hardware failure or disaster. Physically, the transaction log is a file that records the update operations on the database. composition of the transaction log

The SQL Server database engine internally divides each physical file into multiple virtual log files. The virtual log file does not have a fixed size and a fixed number, and these two values are determined dynamically by the database engine.

The transaction log is a looping file. When the database is created, the logical log starts at the starting position of the physical log file. The new log records are added to the back of the logical log until the end of the physical log file. Log interception frees the LSN (log sequential number) from the virtual log records that are older than the minimum recovery log sequence numbers (MinLSN). MinLSN is the minimum logging required to achieve a successful database-wide rollback.

The structure of the transaction log is as follows:

Each virtual log consists of multiple records, each with an LSN (an SQL atomic statement). Analyzing the above figure we can see that the virtual Logs 1 and 2 are intercepted, which indicates that the transactions in virtual Logs 1 and 2 have been successfully committed (it does not mean that the data modification has been physically updated into the database file because there is a problem involving disk IO efficiency).

When the logical log reaches the end of the physical log file, the new log records are looped to the beginning of the physical log, as shown in:

This loop will never end as long as the logical log tail does not exceed the logical log header. If the old log records can be periodically/frequently intercepted, then there will be enough free space for the newly added log records, so that the entire physical log file size remains in a relatively stable range, and conversely, one of the following two scenarios occurs:

1. If auto-grow is set, the physical log file size is increased by the growth percentage/value, which is why our transaction log files often become very large.
2. SQL Server throws a 9002 error if no automatic growth is set or if the disk that stores the transaction log files does not have free space.

Note: If a database has more than one transaction log file (LDF), no other transaction log files will be used unless the first transaction log file has no free space.

transaction log Size maintenance methods Truncate

From the above description we can probably know that the truncate is to identify the transaction log can be recycled logical log files can be reused, the specific trigger conditions are divided into two situations:

1. For a database with simple recovery Mode, the checkpoint operation is performed automatically after each transaction, and the committed logical log space is emptied to ensure that the transaction log file size is minimized. This is equivalent to no logging in the transaction log, so it can be understood that there is no transaction log, which can result in data loss in the event of a disaster.
2. For recovery Mode for Full/bulked-log, each backup Log operation automatically executes the checkpoint operation, emptying the committed logical log space. In other words, we can maintain a controllable transaction log file size through periodic log backups.

Open Query Analyzer and execute the following query:

Now perform a log backup of the database and then execute the loginfo command:

You can see that the status of the logical log is identified as 0, which means that both logical logs 1 and 2 can be reused. However, we observe the offset and size of logical log 4, and the two values do not change, which means that the entire transaction log size remains:

1384448+712704=2,097,152 bytes (pure logical log file size + 8192 bytes header)

Shrink

Shrink is the shrinking log, the Truncate operation does not change the entire transaction log file size, only the original active logical log is marked as inactive for the next use;

The Shrink operation completely destroys the physical structure of the index, resulting in index fragmentation and invalidating the index.

Why is that? Because the data file shrink operation uses the GAM bitmap algorithm to find the largest file in the file each time it executes, it moves it as far as possible to the file header, so repeatedly (like bubbling sort). This completely disrupts the order of the clustered index, causing it to become a disorganized index from an order progression index.

For DBCC SHRINKFILE, DBCC SHRINKDATABASE, and Auto-shrink, they all have the same consequences, introducing index fragmentation, resulting in a lot of I/O operations, CPU consumption, and the overloading of transaction logs.

To see an example, first create a database:

Then query index fragmentation percentage:

By the above you can see that the initial index fragment percentage of only 0.5%, this situation is very good.

We deleted the table we just created and reclaimed the space through the DBCC SHRINK operation:

You can see that the index fragmentation percentage is already close to 100%, so that the index will not only improve the efficiency of our query data, but also aggravate the burden on the system.

Index Fragmentation

SQL Server provides two types of commands to handle the above situation:

A. Rebuild index

Use DBMaint2008; Goalter INDEX all on ProdTable Rebuildgo

B. Reorganize index

Let's look at the effect of indexing a table with an index fragment close to 100% above:

You can see that the index of the database table is restored to normal, but this scenario is not fully recommended, for a large data-intensive clustered index, rebuilding/re-organization index will generate a lot of I/O, CPU consumption, so it is usually good to maintain the index is what we should do.

Summarize

1. Truncate will only make the active part of the virtual log inactive, so that the space can be reused, which does not affect the overall transaction log size; for simple databases, checkpoint checks are performed after each transaction, for Full/bulked-log The database will perform a checkpoint check after each log backup operation.
2. Shrink operations can reduce the physical file size of the log files, but also cause the log files to be re-organized, clustered index and nonclustered index of the original structure will be disrupted, resulting in index fragmentation, and then the impact is index invalidation, disk I/O and CPU resource consumption increased, for Shrink We try to avoid using it, and once we have to use the shrink operation, we also have to decide if we need to rebuild/reorganize the index according to the actual situation.

In summary, it is wise to use periodic log backups to maintain the size of our transaction log files.

SQL Server Transaction Log Truncate && Shrink