The transaction log for database SQDB6 is full due to LOG_BACKUP

This article describes how to approach and resolve specific situation.

Scenario:

Various YSoft SafeQ functions are failing and for example recent days may be missing in the web reports. YSoft SafeQ log files contains errors similar to these:

management-service.log
2020-12-10 07:02:00,041 cml-quartz_Worker-5 ERROR SqlExceptionHelper - The transaction log for database 'SQDB6' is full due to 'LOG_BACKUP'.

db-validator.log
com.microsoft.sqlserver.jdbc.SQLServerException: The transaction log for database 'SQDB6' is full due to 'LOG_BACKUP'.

The article briefly describes the possible cause and a difference between the simple and full recovery model on the MS SQL server. For a detailed description please refer to the official documentation of Microsoft.

Resolution

The error message usually means the MS SQL server hosting YSoft SafeQ databases has ran out of the space or that the limits defined for YSoft SafeQ databases and its transaction logs were reached. To resolve the situation:

  • make sure that the databases are set based on our documentation (e.g. in relation to Snapshot isolation, Contained mode and Recovery model)

  • review that the amount of space available on the MS SQL server is sufficient (drive used for databases as well as the drive where transaction logs are stored)

  • consult the situation and the possible solution with the database specialist of customer

    • sometimes the easy solution is to shrink the transaction log but this usually suits only to the environments with the Recovery Model set to Simple

Below you can find a few details about Simple and Full Recovery Model:

In the environment with disabled "AlwaysOn Availability group" feature on the MS SQL server we recommend to use a Simple Recovery Model (see YSoft SAFEQ server requirements ).

  • The main benefit of Simple Recovery Model is the low amount of necessary maintenance on the SQL server. Databases transaction logs contain records from the last transaction only (SQL query operation) and any new transaction overrides them. Thus the space consumed by the transaction log is reused with every operation and its size is not growing "endlessly". So it is not necessary to take care of database transaction logs maintenance, it is just important to perform regular full database backups. It is good to do those backups at least on a daily basis - the larger the gap between the backups, the more information (configuration changes, statistics, job history, credit operations...) is lost in case of disaster and recovery.

  • When your database server fails, the database recovery process is quite simple to follow, see Recovering Databases .

In the complex environment the "AlwaysOn Availability group" feature is usually enabled on the MS SQL server to support a high-availability and so the Full Recovery Model is required (see Installing YSoft SafeQ Management Server on MSSQL AlwaysOn Availability group).

  • With the Full Recovery Model the SQL server keeps the history of all the queries that were done on the database in the transaction log.

  • The main benefit (compared to the Simple Recovery Model) is that in case of a disaster, it is possible to use those transaction logs to rebuild the database to the last functional state minimizing the data loss.

  • The transaction logs need to be maintained. This is usually nothing difficult for experienced database administrators that are usually available for such an environments. There is no built-in maintenance plan for transaction logs (.ldf file extension) in YSoft SafeQ. Y Soft relies on the customer to manage the MS SQL server itself for example by doing its regular backups of transaction logs. A few details about transaction log file maintenance can be found in the following article:
    https://docs.microsoft.com/en-us/sql/relational-databases/logs/manage-the-size-of-the-transaction-log-file?view=sql-server-2017

    • Y Soft also relies on the customer to be able to restore databases from the available backups and transaction logs on his own - there is no specific method described in the documentation.

  • As there are various operations being performed (e.g. statistical data recalculation), transaction logs may be growing a lot and it is not possible to provide any estimations on its size due to differences of every environment.