Mahedee Hasan

Mahedee Hasan

I am a Microsoft MVP (9th times), programmer, software developer, software architect, consultant, author, researcher, technical blogger, technical speaker, trainer and learner. I have more than 15 years experience in the field of software design and development.

Easy Solution On How to Stop Growing Log File Too Big

3 minute read

In various organizations, huge SQL databases are equipped, which perform more than millions of transactions per hour. A SQL server database has data files and transaction log files. Data files store the user data and transaction log files store all the changes user made in the database and all the details of the transactions performed while making changes.

Now, the issue is that this feature of logging the details, every time changes are made in SQL server can not be controlled or stopped. This causes a grave issue when the size of SQL server overgrows. However, the way in which these log files grow and configure can be controlled. So, to avoid SQL server log file growing unexpectedly, consider any of the following methods given below. Also, to manage the large area size, it is good to shrink log file size, we will discuss the ways to resolve the same issue in this content.

SQL Server- Solutions To Stop Growing Log File Too Big

There are numerous ways for truncating sql ldf too big file. Some of the chief solutions have been provided in the following segment of this content.

  • Monitor default Size Limit: In case SQL ldf file is growing too big then, put a large default size limit on the SQL server, so that it does not expands automatically and overloads the SQL server database.

  • Using the Memory Units: Remember to configure the expansion of log files by making use of the memory units instead of percentage if the SQL transaction log file grows quickly.

  • Changing the recovery model: Simple Recovery model definitely helps in controlling and shrinking the log file size. Based on how crucial the data is, user can choose any of the following recovery models namely,
    1. Simple Recovery Model
    2. Bulk-logged Recovery Model
    3. Full Recovery Model

In the simple model, the most recent backup of the database of SQL server is recovered while in the bulk-logged or full recovery model, database can be recovered up to the point of failure. This recovery is done by restoring transaction log file.

By default, Full recovery model is set. Then, user has to regularly back up the transaction log files to prevent them from becoming too large and to remove inactive transactions from transaction log. Also, consider this when taking back up of .ldf files.

NOTE: If user is defragmenting indexes, make use of DBCC INDEXDEFRAG and not DBCC DBREINDEX. If DBCC DBREINDEX is used then, transaction log file might expand drastically.

Using Manual Solution:

If maintenance is not carried on regularly, log file size grows too big. Therefore, it is recommended to take these manual steps before it takes up all available disk space. Now, let us look at the method to shrink a SQL Database’s transaction log file:

  1. Firstly, Open the SQL Server Management Studio and then log in to the proper SQL instance.
  2. In Object Explorer tree, expand the Database folder » select database with large .ldf file.
  3. After this, Create a full backup of database by right-clicking on it » Select Tasks » Back Up.
    • User should make sure that Backup type is set to Full then, Delete any existing destinations » add a new Disk destination.
    • Browse a location with a lot of free disk space » rename the backup file with the .BAK extension.
    • Choose the bullet option for Overwrite all existing backup sets on the ‘Options’ page.
    • Finally, user can click on the ‘OK’ button to start the process of taking backup of log files.
  4. Similarly, create a transaction log backup of the database in the same manner as done above.
    • Right-click on database » Select Tasks » Backup and assure that backup type is set to Transaction log.
    • Choose the bullet option for Overwrite all existing backup sets on the ‘Options’ page.
    • Finally, user can click on the ‘OK’ button to start the process of taking backup of log files.
  5. The closing step is the shrinking of transaction log files by right-clicking database » Tasks » Shrink » Files

NOTE: User may repeat steps 3,4 and 5 until the .ldf file size becomes physically smaller.

Conclusion

In this content, we have discussed various solutions on how to truncate .ldf files if transaction file size becomes too large. This is necessary in order to manage data with ease. Manual method and some general solutions have been written in this content to enlighten users when they come across such issues while dealing with SQL Server.

You May Also Enjoy