Various YSoft SafeQ functions are encountering failures, resulting in issues such as missing recent days in web reports.
Applies to YSoft SafeQ 6.
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 the difference between the simple and full recovery models on the MS SQL server. For a detailed description, please refer to Microsoft's official documentation.
Resolution:
The error message usually indicates that the MS SQL server hosting YSoft SafeQ databases has run out of space, or the limits defined for YSoft SafeQ databases and their transaction logs have been 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 primary benefit of the Simple Recovery Model is the minimal amount of required 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: Documentation on 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.
- Transaction logs need regular maintenance, a task typically manageable for experienced database administrators who are often available in such 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, such as performing regular backups of transaction logs. A few details about transaction log file maintenance can be found in the following article: Microsoft - Manage the size of the transaction log file . Y Soft also relies on the customer's ability to independently restore databases from the available backups and transaction logs; no specific method is outlined in the documentation.
- Due to the variety of operations being executed (e.g., statistical data recalculation), transaction logs could experience substantial growth. Estimating their size is challenging due to the unique characteristics of each environment.