This article will provides a summary for optimization of SQL database and handle the error due to recovery model of Database.
This article applies to MachPanel v6 and Later.
If the recovery Model for a database is set to Simple then growth will be abnormal, but if the Recovery Model is set to FULL (normally used for point-in-time restore AND/OR HA deployments) then such growth is expected and normal, but needs to be controlled with additional steps.
So if it’s set to FULL then this growth can be tackled by backing-up log (LDF) files on a regular intervals. Log backups make the space available for SQL Server to reuse for further logging.
Different Models are discussed at https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server?view=sql-server-2017
In a nut shell, if Model is set to Full / log files must be backed-up with regular intervals.
Customers and Staff users cannot log into panel:
- The transaction log for database 'MachPanel_Db' is full due to 'LOG_BACKUP'
- Secret Key request failed Error occurred in sending secret key email, please try again later
If the Database Model is set to “FULL” — firstly move it to simple mode then perform a log shrink on that database and move back to Full. To avoid issues in future perform the below steps.
- Perform a full and transaction log backups separately.
- Create SQL maintenance plan to automate the whole process.
- Add daily database backup along with maintenance clean up option
- Add daily log backup, set recursive frequency to 4 or 8 hours along with maintenance clean-up option to save disk space.
Further details can be seen at following articles.
You need to keep an eye on SQL nodes and check the log files at least once a week/Month, if you see and feel abnormal growth of the log files you need to shrink that log file. Preferably, Regular full database backups and transaction log backups are recommended with regular frequency. (discussed above)
DBA routine Checks: Daily /Weekly /Monthly:
- Check Disk Space on every SQL Server
- Check size of databases and make adjustments if needed
- Check/Review and flush logs
- Archive and clean the Windows Event Log
- Perform/Schedule a FULL backup
- Check for updates and if required apply updates to production servers during non-peak hours