"Go" How SQL Server truncates (Truncate) and shrinks (Shrink) transaction logs

How SQL Server truncates (Truncate) and shrinks (Shrink) transaction log classifications: SQL Server database backup restore 2010-01-25 14:321,708 people read comments (4) Report

When SQL Server truncates the transaction log, it simply marks the virtual log file so that it is no longer used, and then prepares it for reuse as a backup (if carried in a full or bulk-logged recovery model). In other words, when using the simple recovery model, the transaction log includes the following logging:

When checkpoint occurs, virtual log files 1 and 2 are no longer used because transactions 1, 2 are already committed, and log records no longer need to be rolled back. SQL Server then reuses virtual log files 1, 2, such as:

This is the transaction log truncation that we are familiar with. Basically, the activity interval for the transaction log has been truncated, but the physical size of the transaction log does not change unless the database uses auto-shrink property settings. In this case, the transaction log is physically periodically shrunk as much as possible.

To physically reduce the size of the transaction log, shrink the transaction log as a known method, and you can choose one of the following options when you use it:

• Execute DBCC shrinkdatabase command
• Execute DBCC shrinkfile command
• Set the transaction log auto-shrink option for a database

It is important to note that the transaction log can only shrink to the boundaries of the virtual log file. Here is an example.

I created a new database that has 1MB of transaction log space and 5MB of auto-growth space. Running DBCC LOGINFO appears as follows:

There are four virtual log files of variable size. Then I enter some data, which causes the transaction log to grow to 5MB:

4 new virtual log files were created in the new 5MB transaction log interval. Each new virtual log file is 1310720bytes and each 7 virtual log files are in use (the status is 2). I now back up the transaction log, so the transaction log will be truncated:

There is currently only one virtual log file in use (line 7th, status 2). If I use the following command now, try to shrink the log to 2M:

DBCC shrinkfile (' Adventureworks_log ', 2)

Because the activity log record is virtual log file 7, SQL Server simply deletes the virtual log file 8. This transaction log shrinks from 7MB to 4.7MB. SQL Server also creates a new false entry in the transaction log in order to remove the most recent activity log record before the 2MB point so that it wraps to the virtual log file 2 (note the line with a status of 2).

If the transaction log is now backed up again, the transaction log will be truncated again, and the active interval is now virtual log file 2.

If I try to shrink the file again now, SQL Server will successfully shrink to around 2MB because the log's active range is close to 2MB. The file is shrunk to the size closest to the log enlistment. The output of the DBCC LOGINFO is as follows:

Transaction log file size is 2359296bytes (total virtual log file size plus 8192 bytes of header information)

So if you find that you can't shrink the transaction log to a specified range, run DBCC LOGINFO, and then check the scope of the virtual log file, figuring out the size of each log, you can shrink the file to what extent.

This article is translated from Sqlbackuprestore, more exciting content please visit http://www.sqlbackuprestore.com