DBCC SHRINKFILE to Clear SQL Server Transaction Log File
A Transaction Log is a file that contains all the records of transactions and the database modifications made by each transaction in SQL Server database. The log file plays a very important part of SQL Server database when it comes to disaster recovery and it should not be in corrupted state. The DBCC SHRINKFILE method is also discussed after understadning the user query “how to clear SQL server transaction log”.
We can recover the database with the help of information present in SQL Server transaction log during any disaster. The transaction log should be truncated or cleared regularly to keep the size of log file from filling up. This page will be discussing on how to clear SQL Server Transaction Log.
Table of Content
DBCC SHRINKFILE to Shrink Transaction Data Files
DBCC SHRIKFILE is basically the command to shrink the database size by shrinking the SQL files, especially transaction log files. It can also move data from one file to another residing in the same file group to remove the original file that is no longer needed.
Why to Clear SQL Server Transaction Log?
During SQL Server work, the transaction log grows if any database changes occur. The regular management of the size of transaction log is necessary to prevent the transaction log from becoming full. Log truncation or clear SQL Server transaction log is required to keep the log from filling up. Here, we can use the DBCC SHRINKFILE method as well as the automated solution too.
The truncation process deletes inactive virtual log files from the logical transaction log, freeing space to be reused by the physical transaction log. The transaction log would eventually fill all the disk space allocated to its physical log files, if it is never truncated. Evidently, SQL server clear transaction log task is required to get more space.
Three recovery models are there in SQL Server. Depending on which one is used, truncation process differs:
Simple Recovery Model The transaction log backups are not supported and the truncation process is automatic and space is available for use.
Bulk-logged Recovery Model No automatic log truncation, backups are needed regularly to mark unused space and make it available for overwriting. Log size can be reduced by using minimal logging for bulk operations.
Full Recovery ModelThe truncation process is same as Bulk-logged Recovery model. There is a high chance of growing log file since every transaction that takes place on the database,is logged into it.
The transaction log space can be monitored using command:
How to Clear SQL Server Transaction Log?
Log truncation frees space in the log file for reuse. Therefore, it is also called Log clearing. The transaction Log file is logically divided into small chunks called Virtual Log Files (VLF). Each VLF file is the unit that can be marked as available for reuse (free) or not available for reuse (used). The VLF is marked ‘active’ if it is used and ‘inactive’ if it is free. Clear SQL Server transaction log means searching and making VLFs as free.
Note: It should be kept in mind that Log Truncation is not meant to reduce the size of the physical log file. Log Shrinking is needed to reduce the physical log file.
Clearing SQL Server transaction log involves two steps. Firstly, we need to perform log backup with TRUNCATE_ONLY option and next step is to use the DBCC SHRINKFILE function to shrink file to the required size.
BACKUP LOG WITH TRUNCATE_ONLY is not a good option as it empties all the contents of our transaction log without backing it up. Many people use this command before shrinking the log file with DBCC SHRINKFILE freeing up the drive space. TRUNCATE_ONLY is not available in later versions of SQL Server.
Instead of truncating transaction logs, we can use simple recovery mode by which we do not generate logs we would not be using.
As you just saw the dbcc shrinkfile log method. We have two options to do so. They are:
Use The SQL Server Management Studio
Right click on the database and choose:Tasks followed by Shrink and then select Files:
Change the file type to log.
Shrink SQL Transaction Log File using T-SQL
For Simple Recovery, following command will be used:
DBCC SHRINKFILE (LogFileName, Desired Size in MB)
For Full Recovery (Only when we don’t mind losing data in log file), the commands to be used are –
ALTER DATABASE nameDB SET RECOVERY SIMPLE GO DBCC SHRINKFILE (LogFileName, Desired Size in MB) GO ALTER DATABASE nameDB SET RECOVERY FULL
Another option to shrink SQL transaction log file is to, backup the database log using the following command:
BACKUP LOG nameDB TO BackupDevice
This is how one can get to know how to clear SQL Server Transaction Log file in order to free up the space in it for further storage of transactions. Transactions are very important to keep track of the changes taking place in corresponding database as well as to rollback just in case it is required. Therefore, always keep a check and maintain the transaction log files.
Also Read: Shrink Transaction File using All Methods
We hope now users are able to make space in their SQL Server database by clearing out the log files. Learning how to clear log file in SQL server using query is easy. However, users must be aware of the right technique. Therefore, the above-mentioned two methods can be a perfect match for users.
Q-1. What is the purpose of DBCC SHRINKFILE command?
Ans: The primary purpose of the SQL Server DBCC Shrikfile is to shrink the physical log file up to the desired limit & fasten the SQL query performance & other operations.
Q-2. What are the risks of using DBCC SHRINKFILE in SQL?
Ans: Several users & experts reported that DBCC SHRINKFILE, causes performance issues but only if not used well. Users need to take care of index fragmentation as well.
Q-3. Do experts recommend shrinking log files?
Ans: Well, it’s not recommended by the experts to shrink the log files as it affects the performance of the server if done falsely. However, in case users are having really heavy SQL log files, there is no harm in adopting this.
Q-4. Are DBCC SHRINKDATABASE & SHRINKFILE different from each other?
Ans: Yes of course, both these are different. Here, the Shrinkfile only allows users to shrink the log data files whereas the other one is to shrink the entire database at once.
Q-5. How to clear the SQL server transaction log without the command?
Ans: Users can simply remove the duplicate files of their database along with corrupted MDF, LDF as well as NDF files.