This article describes experience with database transaction log size issues when running Vertec on-premises on Ms sql server. The measures formulated may affect the recovery of databases from backups and should therefore be carefully considered. Transaction log settings on the Vertec database as well as setting up backups are the sole responsibility of the customer when running Vertec on-premises.

Microsoft’s SQL Server is designed in such a way that all the transactions of each database are written into a corresponding log file. Theoretically, it would be possible to reconstruct the exact state of a DB at a certain point in time using the log file and an older backup of the database. Experience has shown that this is never done in practice. If a backup actually needs to be restored once, it is usually done with a complete backup of the database. This restores the state at the time of the backup.

Experience has shown that there are always problems with the size of the log file: If you leave the default settings on the SQL server, the log file grows indefinitely and fills up the disk space. If the disk space is full, the SQL server can no longer accept transactions and the SQL server stops working.

To avoid problems with large log files, you can run the Vertec database in the SIMPLE recovery mode instead of FULL. This prevents the log files from growing indefinitely. However, in the SIMPLE mode, it is absolutely essential to make backups of the database. The two relevant recovery modes are explained in more detail below.

The transaction log grows indefinitely and thus endangers the system stability, because when the total disk space is reached, the SQL server will not accept any more transactions – Vertec will not run anymore. If this recovery model has been chosen, then you have to take care of managing the size of the transaction log.

The best way to do this is to perform a backup of the transaction log immediately after a normal backup of the database. When backing up the transaction log, it can be truncated immediately (TRUNCATE). This is safe, since the entire log between 2 backups is now on the backup.

• Advantage: Restore (if the transaction log is also on the backup) allows you to restore the state of the database at any time. However, even with such a restore, you lose all changes that took place after that time.
• Disadvantage: You have to take care of the transaction log. If you do not take care of the transaction log, at some point the SQL server will no longer accept transactions and you will have a total downtime.

Only the active part of the transaction log is kept, the inactive part is deleted. A so-called CHECKPOINT (which is set, for example, by a backup of the database) separates the inactive part from the active part. Thus, only the transaction log since the last backup is kept. However, this cannot be backed up and restored manually. With this recovery model, the last backup is always available.

• Advantage: No need to manage the transaction log. Total downtime unlikely due to the size of the transaction log.

• Disadvantage: The database can only be created at the time of the last backup.

Since only entire backups are usually played back, the SIMPLE mode is usually sufficient (even with the SIMPLE model, you can restore at any time after the last backup). The settings regarding transaction logs on the Vertec database and the setting up of backups are the sole responsibility of the customer.

If you already have a very large log file on an existing database and want to reduce it, experience has shown that the following two options have proven successful:

If you only back up the transaction log on the SQL server, you can reduce the size of the log file considerably. This method is only possible if the recovery mode of the database is set to FULL, in SIMPLE mode this option is not available. For backup you specify as backup type Transaction Log and for options you set “Truncate the transaction log”. This reduces the size of the transaction log to a minimum.

It is best to define the backup concept by default so that every backup in Recovery Mode FULL necessarily includes the backup and truncate of the transaction log.

This procedure is only possible if no one is accessing the database, as it has to be taken completely offline. Before doing this, it is important to make a complete backup of the DB.

Detach or detach the database from the server (via right mouse button on the DB > Tasks > Detach). After that, the log file belonging to the DB must be deleted or unnamed. Next, the DB is re-added via Databases > Attach, by selecting the database file belonging to the DB with the extension *.mdf and removing the log file with the extension *.ldf from the database details. In this way, the SQL server creates a new, almost empty log file for the database.