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.
- Microsoft SQL Server 2005 and higher
- All supported environments
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.
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:
- Open the SQL Server Management Studio.
- Browse to Local Server > Databases > DatabaseName > Properties > Options > node
- 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.
- Open the SQL Server Management Studio.
- Log in to the instance where the GFI Software databases are stored using the 'sa' credentials.
- Expand the Instance name > Databases.
- Right-click on the database name and select Tasks > Shrink > Files.
- Under File Type select Log.
- Under Shrink Action, select Reorganize pages before releasing unused space and click OK.
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.
- Stop all GFI Archiver services.
- Open the file with Notepad: ..\GFI\Archiver\Store\Data\product.config
- 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:
<add key="RecoveryModel" value="Simple"/>
- Save the file.
- Start all GFI Archiver services.