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
3. Transaction (Process ID xxx) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
   

Solution

File Size Recommended Configuration

• The initial DB file size for Data and Log should be large enough to handle data, growth should be set at 10% auto and Max size is optional.
  • To set size, use Shrink files menu and see what is current used space, the initial size for Primary Data file should be 10% more of current used space.
    • And for Log, recommended value is double the size of Primary Data file or equal to Primary Data file size.

​​​​​

Use below Script to do above steps easily:

ALTER DATABASE [MachPanel_DB]
    MODIFY FILE ( NAME = N'MachPanel_DB', FILEGROWTH = 10%,  MAXSIZE = UNLIMITED )
GO

ALTER DATABASE [MachPanel_DB]
    MODIFY FILE ( NAME = N'MachPanel_DB_Log', FILEGROWTH = 10%,  MAXSIZE = 2GB )
GO

Shrinking of Files Using Scheduler

• When shrinking Data or Log file, make sure to follow below:
  
  • Make sure available free space is around 10%.
  • Use option: Reorganize pages before releasing unused space
    
    • Shrink file to: Value should be: Current used space + 10%.
    • In example below: 
      
      • Allocated: 1188.25 MB
      • Available Free Space: 113.31 MB
      • So Shrink File To value should be: 1182 MB ((1188.25-113.31) * 10% + (1188.25-113.31))

​​​​

Note: The default option “Release unused space” causes Log file to reduce to 1MB, that impacts application performance badly and queries where bulk number of insert operations are done can be halted or dead locks created.

Furthermore:

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