How to Clear SQL Server Transaction Log File with DBCC SHRINKFILE

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 understanding 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 discuss how to clear SQL Server Transaction Log.

Table of Content

• What is DBCC SHRINKFILE
• Why Clear SQL Server Transaction Log
• How to Clear SQL Server Transaction Log?
• SQL Server Management Studio Solution
• The T-SQL Command Solution
• The Conclusion
• F.A.Q

DBCC SHRINKFILE to Shrink Transaction Data Files

The DBCC SHRIKFILE is basically the command to shrink the database size by shrinking the SQL files, especially transaction log files. DBCC SHRINKFILE log 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.

Now, let’s have a look at the syntax & understand shrink log file SQL Server components one after another:

DBCC SHRINKFILE   
(  
    { file_name | file_id }   

    { [ , EMPTYFILE ]   

    | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]  

    }  
)  
[ WITH 
  {     
      [ WAIT_AT_LOW_PRIORITY 
        [ ( 
            <wait_at_low_priority_option_list>
        )] 
      ] 
      [ , NO_INFOMSGS]
  }
]

< wait_at_low_priority_option_list > ::=  
    <wait_at_low_priority_option>
    | <wait_at_low_priority_option_list> , <wait_at_low_priority_option>

< wait_at_low_priority_option > ::=
    ABORT_AFTER_WAIT = { SELF | BLOCKERS }

Now, let’s understand this SQL Server clear log file command in depth:

• file_name – The logical name of the file that needs to be shrunk.
• file_id – The identification number of the file needs to be shrunk.
• target_size – A number (integer) representing the new megabyte size.
• EMPTYFILE – EMPTYFILE in SQL Server clear transaction log cmd migrates the data from a specified file to other file within the same filegroup. 
• NOTRUNCATE – Moves allocated pages to the non-allocated pages with certain clauses.
• TRUNCATEONLY – It releases all free space at the file’s end to the OS. However, it does not perform any file movement within the file.
• WITH NO_INFOMSGS – To suppress all of the informational messages.
• WAIT_AT_LOW_PRIORITY – Prevent new queries requiring schema stability locks to get blocked.
• ABORT_AFTER_WAIT – Exit the shrink file operation without any action & also kill the user transaction blocking the operation.

DBCC SHRINKFILE Log User Query in Depth

Now, we are going to have a look at the real user query in depth. This can help us find out how a user faces difficulties in learning how to clear transaction log in SQL Server database. After that, we will determine the reasons followed by the solutions.

As we can see, the user tried two different solutions but is still unsure about both. It happens when users do not have in-depth technical expertise. Therefore, the below-mentioned solutions can help users get the perfect solution.

Why to Clear SQL Server Transaction Log? Reasons to Shrink Log File SQL Server

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.

Learning how to clear transaction log in SQL Server is not tough but a bit technical. 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

Conclusion

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.

SQL Shrink Log File FAQs

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.