Phone Lines icon GFI Support

Articles in this section

See more

Microsoft SQL Server Database Growing Large

Author: Timothy Parker Updated

Overview

Each record in the GFI Software database may add data to the transaction log file (LDF) of the same database. Therefore, when using GFI Software products, the size of the Microsoft SQL back-end databases may grow too large. This article provides steps to reduce Archive size based on the recovery model. 

 

Prerequisites

 

  • Microsoft SQL Server 2005 and higher
  • SQL Server Management Studio

 

Solution

 

Do Not Enable the Auto-Shrink Option on Microsoft SQL Databases

Microsoft Software no longer recommends this option be used. Instead it is recommended that you configure it according to the Recovery Model Options. To verify this, follow the steps below

  1. Launch SQL Management Studio
  2. Expand Databases in the Object Explorer
  3. Locate the database in question
  4. Right click and go to Properties
  5. Select Options Page
  6. Under Automatic, Locate Auto Shrink and confirm it is set to False
    autoshrink.png

SQL Recovery Model Options

A Microsoft SQL Server database includes two files:

  • The database file (DatabaseName.mdf)
  • The transaction log file (DatabaseName_log.ldf).

The total database size reported in Microsoft SQL (under DatabaseName > Properties > General) is the sum of both these files. You can also see the current individual size of the files under the DatabaseName > Properties > Files > Initial Size.

The transaction log, in particular, can grow very large when executing a significantly large number of transactions at one time. Therefore, it is important to manage the size of the transaction log efficiently.

You can change the recovery options in Microsoft SQL Server 2005 and later versions as follow:

  1. Open the SQL Server Management Studio.
  2. Browse to Local Server > Databases > DatabaseName > Properties > Options > node
  3. Under the Recovery model dropdown, you will find three options:
    • The 'Simple' model allows the data in the transaction log file to be overwritten once it is committed to the database. However, when there are a large number of transactions in a short period of time the transaction log may balloon in size and will NOT be shrunk automatically by Microsoft SQL server. This model only allows you to recover the database at the last point a full backup was performed.
    • The 'Full' recovery model allows you to recover the database at any point in time because the transaction log entries are not allowed to be overwritten. The log is automatically shrunk when a backup (Full or incremental) is done.
    • The 'Bulk-logged' option is only for imports of data from external sources and so is not applicable.

GFI Software recommends that you use a "Full" recovery model set up for your database, including a scheduled of Full Backups / incremental backups. It is allowing you to recover to any point in time if the database becomes corrupted. It will automatically shrink the transaction log file during each backup, releasing the space used by the data.

IMPORTANT NOTE: Backups can be scheduled in SQL Server Management Studio under the Maintenance node using the Maintenance Plan Wizard. However, SQL Server EXPRESS versions do not have this option. In order to set up backup plans in SQL Server EXPRESS instances, you can follow the procedures in this Microsoft article: How to schedule and automate backups of SQL Server databases in SQL Server Express.

 

Truncating the Transaction Logs in Microsoft SQL Server Manually

 

Note that truncating the transaction logs in this way might affect your backup plan.

  1. Open the SQL Server Management Studio.
  2. Log in to the instance where the GFI Software databases are stored using the 'sa' credentials.
  3. Expand the Instance name > Databases.
  4. Right-click on the database name and select Tasks > Shrink > Files.
  5. Under File Type select Log.
  6. Under Shrink Action, select Reorganize pages before releasing unused space and click OK.

GFI Archiver

In GFI Archiver it is possible to specify the recovery model for newly created archive stores:

  • When upgrading to a newer version of GFI Archiver, installation files mentioned in this procedure will be overwritten with default versions making the changes void and ineffective. It is therefore suggested to keep a record of this procedure and follow it once again directly after upgrading to keep this functionality intact.
  • This procedure requires to edit files manually. If written incorrectly, it can leave the server in a non-operational state. Please keep backups of any data which is edited throughout this article before saving any changes to them.
  1. Stop all GFI Archiver services.
  2. Open the file with Notepad: ..\GFI\Archiver\Store\Data\product.config
  3. Search for the following line: <add key="RecoveryModel" value="Default"/>
    This key's value defines the recovery model used, it can be any one of the following:
    • Default
    • Simple
    • Full
    • Bulk_Logged
    For example, change the line to the following in order for new archive stores being created using the simple recovery mode: <add key="RecoveryModel" value="Simple"/>
  4. Save the file.
  5. Start all GFI Archiver services.

 

Testing

 

After performing truncation of the SQL database, you can check the size again under DatabaseName > Properties > General. 

If you have changed the default Recovery model, then when the next Archive Store database is created (based on the schedule configured in Archiver Console > Configuration > Archive Store > New Archive Store Settings) you can then check the Properties of the newly created database and confirm what Recovery Model is in use. 

  1. Open the SQL Server Management Studio.
  2. Browse to Local Server > Databases > DatabaseName > Properties > Options
  3. Review the current setting to confirm what is in use

Related articles