Grab Deal : Flat 30% off on live classes + 2 free self-paced courses! - SCHEDULE CALL

- SQL Server Blogs -

How to Clear SQL Server Transaction Log File with DBCC Shrinkfile



Introduction to SQL Server Transaction Log

In SQL, the transaction log file is used to save records that are produced during the logging process in a SQL Server database. The transaction log is basically an integral part of SQL Server database. It is not only integral but also a most important component of the SQL Server database especially when it is about disaster recovery. As in SQL, as soon as the database is modified the log record is written to the transaction log. Here all the changes are recorded in the transaction file in sequential order. In this article, we are going to discuss the SQL Server transaction log and its features.

Transaction Log Storage

SQL Server transaction log stores every transaction except a few ones like SELECT INTO and BULK IMPORT operations. Internally transaction log is divided into smaller parts that are called Virtual Log Files or VLFs. There are more than one VLF and when one file is filled with log details than logs are written to the next available file. These transaction log files are represented in circular form so when one file reaches the end than it starts again from the beginning. Here, the truncation process is used to mark the end of file or any unused part of the file so that it can be reused for storing information.

Read: How to Compare MongoDB and DynamoDB?

In the following cases, any log record is marked as unused in Transaction log:

  • The transaction of which it was a part has been committed
  • The database pages for which they have been created have written to the disk
  • It is not needed for full, differential, or log backup
  • It is not required for any feature that needs a log.

We can recover the database at any time when a disaster occurs. The user must regularly clean the transaction log to keep pace in the log file.

Learn SQL Server in the Easiest Way

  • Learn from the videos
  • Learn anytime anywhere
  • Pocket-friendly mode of learning
  • Complimentary eBook available

Why should we clear the Transaction log?

If any database change occurs than SQL Server transaction log grows. Regular maintenance of transaction log can prevent it from becoming full. Log truncation technique is used to keep the log empty or prevent it from being filled up. This process deletes the inactive log files from the logical transaction log and freed up space from logical transaction log that can be reused by the physical transaction log. If the log will not be truncated then it can fill all disk space allocated to the physical transaction log.

Truncation process for the three recovery models of SQL is different and explained as below:

  • Simple Recovery Model: In this model Transaction log backup is not supported and truncation process executes automatically as a result space is freed up automatically
  • Bulk-logged Recovery Model: In this automatic truncation is not available and so regular backups are needed to mark unused space and hereby space is created for overwriting purpose. The size of the log file can be here reduced for bulk operations
  • Full Recovery Model: In this model again, the truncation process is not automatic and the chances for growing the log files are higher.

Read: SSIS Tutorial for Beginners

One can know the available and used transaction log space with the help of a single line command as shown in the following figure: 

Now after getting the information, you can take action accordingly to clear the log file.

How to clear the Transaction Log File of SQL Server?

As it has been clear so far that log truncation process creates more free space in the transaction log file so is also called log clearing. A transaction log file is usually o small chunks that are also called Virtual Log Files or VLF. These VLFs are marked if are available for reuse and if not, then also they will be marked accordingly by using active and inactive tags respectively.

Register for the demo class now and learn all about the SQL Server Transaction log with our certified mentors!

To clear the transaction log, one has to search these VLF files and make them free or available. To reduce the size of physical log files, Log Shrinking process is used as Log Truncation does not reduce the physical log storage size.

SQL Server Training & Certification

  • No cost for a Demo Class
  • Industry Expert as your Trainer
  • Available as per your schedule
  • Customer Support Available

Two steps are involved in the process of clearing SQL Server transaction log:

  1. TRUNCATE_ONLY option is used to perform a log backup
  2. DBCC SHRINKFILE function shrinks the file to the required size

Here BACKUP LOG TRUNCATE_ONLY option can also be used for this process but it empties all transaction log content without backing it up. As TRUNCATE_ONLY option is not available in advance version of SQL Server, so many users use this option along with DBCC SHRINKFILE function.

Read: SSIS Tutorial for Beginners

Simple recovery model can be utilized to generate the logs that will be used rather than opting truncation transaction logs. Like shown in the following window: 

SQL Server Transaction Log File with DBCC Shrinkfile

Here, two options can be used to shrink the log file:

  1. Shrinking of Log File by using SQL Server Management Studio or SSMS
  2. Shrink the transaction log file by using T-SQL

In the above-mentioned option, SSMS is a server management tool that is easier to use can instantly reduce or shrink the size of log files. Here the database for which the shrink operation has to be performed can be chosen and by the right-clicking on it Shrink operation can be performed as shown in the following figure: SQL Server Transaction Log File with DBCC Shrinkfile

Here in this tool, you may have to choose the file type as log: SQL Server Transaction Log File with DBCC Shrinkfile

If you want to shrink the file using T-SQL then the following command will have to be used:

DBCC SHRINKFILE (Log Filename, Desired Size in MB)

In case of Full Recovery Model, when we are not bothered about losing data then the following commands can be used for shrinking the file:

ALTER DATABASE nameDB SET RECOVERY SIMPLE GO DBCC SHRINKFILE (Log Filename, Desired Size in MB) Go ALTER DATABASE nameDB SET RECOVERY FULL  

Another option is also available to shrink the SQL file and that is by using a command.

Read: How To Quickly Get Entry Level SQL Jobs

BACKUP LOG nameDB TO Backup Device

In this way, anyone can clear the transaction log file of SQL Server to create free space in the log and reuse that space. As transactions are very much important and the user must keep track of them to make changes in the transaction and to keep the space available for further storing.

Conclusion

Here, we can say that as transactions are an imperative and essential part of SQL Server so one must keep track of them. But as many transactions take place so to improve the efficiency of transaction processing, it is must keep clean the transaction log files. Clear files will not only provide free space for information storage but also will improve the performance of SQL Server. As per the recovery model used by SQL Server, users can perform these operations. The user can clean the log in three ways to choose the best one and keep the log clean and empty.

Read: Top 75 MySQL Interview Questions and Answers for Freshers , Intermediate & Experienced (2024)

fbicons FaceBook twitterTwitter lingedinLinkedIn pinterest Pinterest emailEmail

     Logo

    JanBask Training

    A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.


  • fb-15
  • twitter-15
  • linkedin-15

Comments

Trending Courses

Cyber Security Course

Cyber Security

  • Introduction to cybersecurity
  • Cryptography and Secure Communication 
  • Cloud Computing Architectural Framework
  • Security Architectures and Models
Cyber Security Course

Upcoming Class

10 days 02 Aug 2024

QA Course

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing
QA Course

Upcoming Class

14 days 06 Aug 2024

Salesforce Course

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL
Salesforce Course

Upcoming Class

2 days 25 Jul 2024

Business Analyst Course

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum
Business Analyst Course

Upcoming Class

17 days 09 Aug 2024

MS SQL Server Course

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design
MS SQL Server Course

Upcoming Class

3 days 26 Jul 2024

Data Science Course

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning
Data Science Course

Upcoming Class

10 days 02 Aug 2024

DevOps Course

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing
DevOps Course

Upcoming Class

-0 day 23 Jul 2024

Hadoop Course

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation
Hadoop Course

Upcoming Class

10 days 02 Aug 2024

Python Course

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation
Python Course

Upcoming Class

4 days 27 Jul 2024

Artificial Intelligence Course

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks
Artificial Intelligence Course

Upcoming Class

18 days 10 Aug 2024

Machine Learning Course

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning
Machine Learning Course

Upcoming Class

31 days 23 Aug 2024

 Tableau Course

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop
 Tableau Course

Upcoming Class

10 days 02 Aug 2024

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews