Shulou(Shulou.com)05/31 Report--

SQL Server how to truncate and shrink the transaction log, many novices are not very clear about this, in order to help you solve this problem, the following small series will explain in detail for everyone, there are people who need this can learn, I hope you can gain something.

When SQL Server truncates the transaction log, it simply marks it in the virtual log file so that it is no longer in use and is then ready to make backups in reusable form (if carried in a full or bulk log recovery model). That is, 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 and 2 have already been committed and logging no longer needs to be rolled back. SQL Server then reuses virtual log files 1, 2,

This is known as transaction log truncation. Basically, the activity interval of the transaction log has been truncated, but the physical size of the transaction log does not change unless the auto-shrink property setting is used. In this case, the transaction log is physically shrunk as periodically as possible.

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

Execute DBCC SHRINKDATABASE command

Execute DBCC SHRINKFILE command

Set the transaction log auto-shrink option for the database

Note that transaction logs can only shrink to the boundaries of virtual log files. Here's an example.

I created a new database with 1MB of transaction log space and 5MB of auto-growth space. Running DBCC LOGINFO displays the following:

There are four variable-size virtual log files. Then I enter some data that will grow the transaction log to 5MB:

Four new virtual log files are created in the new 5MB transaction log interval. Each new virtual log file is 1310720bytes, and every seven virtual log files are in use (status 2). I am backing up the transaction log now, so I will truncate the transaction log:

Did reading the above help you? If you still want to have further understanding of related knowledge or read more related articles, please pay attention to the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.