For every SQL Server user, log files bear extreme importance. Transaction log files are the files used to save all the transaction data that take place in the SQL database. The significance of these files can be fully realized during any disaster when the data get corrupt or damaged. With the help of Log files, the damaged and lost data can be easily recovered. Continue reading to know the difference between Truncate vs Shrink file management option.
Depending on the size of your SQL database, these log files may occupy a large portion of database. Therefore comes the requirement of transaction log file management. Often, users get confused while managing their database as there are two options in front of them: truncate and shrink. This write-up will tell you the difference between truncate vs shrink; at the same time describe how to apply them.
Difference Between SQL Database Log File Truncate vs Shrink
Many users believe in the notion that to truncate and to shrink log file are basically the same thing and have the same effect. Well, this is not right. Both the processes are different and have a different impact on the log files. When you truncate a log file, the status of the virtual log file will convert into inactive from active. At the same time, space will be open for reuse. On the other hand, log file shrink will reduce the size of physical log file. This process will also increase the database fragmentation. It is always better to truncate than to shrink, so you should go for shrink option only if you do not have any other choice.
The Syntax of SQL Log File Truncate
The purpose of log file truncate is to prohibit the log file from filling up the database space. If you do not truncate the log file regularly, all the space allocated for the physical log file will be consumed. In this process, the SQL transaction log file is divided into Virtual log files(VLF), which is actually the smaller portions of the log file. During the truncate process, the actual log file gets deleted from the disk and gets overwritten.
This process called log file truncate will change the status of one or multiple Virtual log file from active to inactive. Once the inactive status is set, space is marked as ready for reuse and gets automatically used. Depending on the settings of SQL Server, these are the common situations when SQL Server reuses the space automatically.
- The automatic reusing will happen if the SQL database is in simple recovery model.
- This will also happen when users conduct the log file backup under either FULL or BULK LOGGED Recovery model.
Users can implement Truncate_Only or With No_Log commands for manually log file truncate. Here is the syntax you should use for this purpose.
The Syntax of Log File Shrink
As the name suggests, shrinking reduces the transaction log file size. But the main issue with shrinking process is that it empties the files and at the same time, export it to a different location of the same file group. So, by using this technique, users are having their log files in a compact size. As we have discussed earlier, it is better to avoid shrinking and go for log file truncate. Here is what you can do in order to avoid shrinking:
- Make sure to backup transaction log files in a regular interval. Also, try to keep the log file size as small as possible.
- If your organization does not mind some amount of data loss, you can run the differential log file backup in simple recovery model. This will keep the file size small and help you maintain the files.
- Use alternate databases. Transfer the log file in a new drive.
Here is the syntax you have to use in order to shrink the transaction log file:
The aim of this post is to make the difference between log file truncate vs shrink clear to the readers. Hence, we have included a detailed description of both the topics along with the syntaxes needed for truncate and shrinking. Since truncate frees up the actual space used by the log files and truncate results in fragmentation, we recommend users to go for truncate above shrink.