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

Original: http://blog.csdn.net/tjvictor/article/details/5253931

?

when SQL Server when the transaction log is truncated, 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 log recovery model ) . In other words, when using the simple recovery model, the transaction log includes the following logging:

when Span style= "COLOR: #336699" >checkpoint 1 2 1 , 2 has been submitted, and the log records no longer need to be rolled back. Then sql Server reusing virtual log files 1 , 2

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:

• Execution DBCC shrinkdatabase Command
• Execution 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've created a new database that has 1MB of transaction log space, 5MB 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:

in the new 5MB New in the transaction log interval 4 a new virtual log file. Each new virtual log file is 1310720bytesand every 7 virtual log files are in use ( 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 7, status 2). If I now use the following command, try to shrink the log to 2M :

DBCC shrinkfile (' Adventureworks_log ', 2)

7 sql Server Just delete the virtual log file 8 7mb shrunk to 4.7MB. SQL Server also creates a new false entry in the transaction log in order to remove 2MB 2 . Span style= "font-family: Arial" > (note that the status is 2 rows).

?

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

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

the 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 , then check the scope of the virtual log file, figure out the size of each log, and you can shrink the file to what extent.

??

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

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