Page 1 of 1

Why is the SQL Transaction Log growing exponentially?

PostPosted: Mon Jan 25, 2016 8:58 am
by renes
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.

So if there are a lot of transactions entered into a system the transaction log can grow quite a bit. However, it is possible that the transaction log grows unexpectedly quickly although there aren't entered too many transactions. If this is the case then it is most likely that 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'
Screenshot attached.