Search

MachPanel Knowledgebase

INFO:Recovery Modes and the Performance Matrix for SQL Database

Rehan Waseem
MachPanel

Summary

This article will provides a summary for optimization of SQL database and handle the error due to recovery model of Database.

Applies To

This article applies to MachPanel v6 and Later.

Overview

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.

Possible Errors

Customers and Staff users cannot log into panel:

  1. The transaction log for database 'MachPanel_Db' is full due to 'LOG_BACKUP'
  2. Secret Key request failed Error occurred in sending secret key email, please try again later

Solution

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.

  1. Perform a full and transaction log backups separately.
  2. 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.

https://social.technet.microsoft.com/wiki/contents/articles/39148.sql-server-2016-create-log-backup-maintenance-plan.aspx

https://www.mssqltips.com/sqlservertip/6249/backup-database-task-in-sql-server-maintenance-plans/

Important Note

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:

  1. Check Disk Space on every SQL Server
  2. Check size of databases and make adjustments if needed
  3. Check/Review and flush logs
  4. Archive and clean the Windows Event Log
  5. Perform/Schedule a FULL backup
  6. Check for updates and if required apply updates to production servers during non-peak hours
Details
Type: INFO
Level: Beginner
Last Modified: 6 Months Ago
Last Modified By: rehan_waseem
Article not rated yet.
Article has been viewed 467 times.
Options
Also In This Category
Tags