Database Maintenance - Softrip Recommendations

Owned by Vincent Josset
Last updated: Jul 09, 2024
2 min read

Database maintenance is a vital part of any application upkeep. For clients who host their own Softrip instance, maintenance is left up to their IT or DBA team.

This document describes Softrip's standard recommendations for database maintenance. These are the processes in place for Softrip's own hosting environment.

Note that these are Softrip recommendations that may or may not fit your own data retention policies and practices; feel free to adjust them as needed.

 

Regular Database Maintenance

 

Indexes

Softrip recommends rebuilding all table indexes once per week, generally on the weekend at night (for example 09:00 PM on Saturdays).

This can be done with a standard SQL maintenance task "Rebuild Indexes".

Statistics

Softrip recommends recalculating tables statistics once per week, generally on the weekend at night (for example 09:00 PM on Saturdays).

This can be done with a standard SQL maintenance task "Recalculate Statistics".

 

Database Backups

 

Transaction Logs

Softrip recommends backing up and truncating database transaction logs once every hour. Since differential database backups are run every hour, these transaction log backups can be replaced every time they are run (i.e. only keep one single transaction log backup file, overwritten every hour).

Full Backups

Softrip recommends running a full database backup once a day, during off-peak hours (e.g. at night).

These full backups should be available locally on the database server for 7 days. Keeping 7 days of full database backups can help quickly restore deleted data in case a user removes or changes records by mistake.

Once a day, after the local database backup, daily full backups should be pushed to offsite long-term storage (e.g. S3 or similar).

Differential Backups

Softrip recommends running differential database backups every hour. These differential backups should be available locally on the database server for 7 days. These differential backups are not typically pushed to offsite long-term storage.

 

Sample Maintenance Schedule

 

The Softrip standard database maintenance schedule is as follows:

  • Weekly - Sat - 21:00

    • Rebuild Indexes

    • Recalculate Statistics

  • Daily - 00:00

    • Full database backup

    • Delete local backup files older than 7 days

  • Daily - 01:00

    • Push local full database backups to long-term storage

  • Every 01:00 on the half-hour (Starting at 00:30 and ending at 23:30)

    • Database differential backup

    • This schedule starts at 00:30 and ends at 23:30 to avoid having a differential backup run at the same time as a full backup

  • Every 01:00 on the hour (Starting at 00:00 and ending at 23:00)

    • Backup and truncate transaction logs