SQL Transaction Log growing exponentially

Owned by Beth Blok (Unlicensed)
Last updated: Mar 16, 2018 by Mike Sheen
1 min read

Problem

The database log files just continue to grow until it takes all available disk space and the log needs to be shrunk.

Solution

A transaction log is a file – integral part of every SQL Server database. It contains log records produced during the logging process in a SQL Server database. The transaction log is the most important component of a SQL Server database when it comes to the disaster recovery – however, it must be un-corrupted. After each database modification – transaction occurrence, a log record is written to the transaction log.  All the changes are written sequentially. 

Where the growth of this log exceeds what would be expected then it is probably because the 'Recovery Model' of the database is set to 'Full' or 'Bulk-Logging' instead of 'Simple' logging. 

To change this setting follow the steps below: 

  • Load SQL Server Management Studio

  • Right Click on the database where the transaction log grows exponentially

  • Select Properties

  • Select Options in the left hand pane

  • Under the Recovery model: drop down menu select 'Simple'

  • Click 'OK'

Related articles