41520members
184570posts

Database size is growing rapidly, SQL Database Recovery Modes Explained

Database size is growing rapidly, SQL Database Recovery Modes Explained

Issue

  • LDF file of a database is growing rapidly.
  • SQL Database Recovery Modes Explained

Product Line

Andover Continuum

Environment

  • Continuum 
  • SQL Server

Cause

Recovery Mode setting could allow all transactions to be logged.

Resolution

The Full Recovery Mode basically logs all transactions successfully, failed, table creations, columns created etc... When the Continuum database is created, there are no additional tables added or columns added unless there is an update to the database to a different version. Some sites will require Full Recovery Mode but for most sites, we only need to know when an error occurs when updating or altering rows occurs and Simple Recovery Mode will log those.

The Continuum Databases are created with Simple Recovery Mode. It is up to the individual site whether they need Full Recovery Mode. Most sites run with the default Simple Recovery Mode since there is no need to log every successful transaction but only want to catch the transactions that have an error.

The Recovery Mode is especially important if your site is a single-user setup. Single user databases are limited in size and if you fill-up the .LDF log file, you can exceed Microsoft's database limitations in a short period of time. As for Enterprise versions of SQL for Continuum Lan systems, you'll need to watch your available disk space.

Check what the Recovery Mode is set to by using SQL Server Management Studio Express, right-click your ContinuumDB and select properties, go to the Options tab, and check the Recovery model. See the image below for details.

 

Furthermore, if you have selected Full Recovery Mode in the past and now have a large unwanted .Ldf file that is causing you issues, then selecting Simple Recovery Mode at this point will also truncate your original Log file down to a more usable size retrospectively.  (Although it may take a few hours before you see any changes).

Tags (2)
Labels (1)
No ratings