Database Maintenance plans
- We do recommend that you routinely maintain your database. Below are a few guidelines, but keep in mind that every business is unique, and your infrastructure should be evaluated by a database professional to ensure your server settings are tailored to fit your company’s specific needs.
- Reorganize data and index pages on a regular basis. Leave Change free space percentage to 10%.
- Select Check database integrity and select attempt to repair any minor problems. This should be run on a regular basis.
- Be sure to backup your database regularly. There are many third party software applications that will automate this for you i.e. BackupExec. We generally recommend a full backup nightly. If you need point-in-time recovery, keep in mind that you will need to run SQL in full recovery mode and execute transaction log backups (hourly transaction log backups with full daily backups is common). Running SQL this way will typically increase the database size on disk by around 5%. This is generally only recommended if there are frequent changes to the site with high-volume scanning where losing a day of work is costly.
- We typically suggest rebuilding indexes on the database once you have exceeded a page count of 10 and a fragmentation level of 30%. Weekly rebuilds are generally sufficient though extremely high file/document throughput or high volume workflow may warrant more frequent (nightly/bi-weekly) index rebuilds.
- If the database is in full recovery mode, ensure that transaction log backups are run regularly, if you do not, the transaction log will grow uncontrollably causing you to run out of hard disk space and reduce performance.
- Run backups on a schedule that you’re comfortable with recovery. If nightly backups are sufficient (you’re willing to accept 24 hours of lost work) set the databases to simple recovery mode and don’t worry about transaction log backups, and run the backups nightly.
- Check Database integrity on same schedule as your full backup.
- Rebuild Files, Documents, and DocumentRoute table indexes nightly (if you’re importing large amounts of metadata, images, or workflow)
- Rebuild all Indexes weekly. This re-constructs corrupt or missing indexes in place on the server. A corrupt index can cause unexpected search results, either in the form of an error or returning more or fewer matches than it should.
- Update Statistics regularly. Weekly should be sufficient. If you have a high amount of through-put of files or documents, then nightly might be best, at least on those specific tables (dbo.files and dbo.documents).
- Note: Refer to the Microsoft SQL documentation to learn more about SQL maintenance plans.
See FileBound Database Maintenance Scripts here.
Helpful Articles
https://dba.stackexchange.com/questions/44889/simple-or-full-recovery-model-for-databases