Sonoma Partners Microsoft CRM and Salesforce Blog

MSCRM Database Maintenance and Configuration Tips Checklist

Today's blog post was written by Rob Jasinksi, Principal Developer at Sonoma Partners.

If you are running Microsoft Dynamics CRM On-Premise, you also have to maintain and support the database that it uses. In this post, I have put together a list of some basic best practice tips that you should consider for your local CRM databases.

MSCRM uses a database for each organization plus a general configuration database that all organizations share. The naming conventions are generally [OrgName]_MSCRM for the organization database plus MSCRM_CONFIG for the configuration database. This article will focus on the organization database, but the same tips and best practices can also be applied to the configuration database.

Configuration Tips

  • Have the database data files (mdf) and log files (ldf) on separate disks or volumes. Both files are heavily used during normal CRM use and having the files on separate disks will spread the disk I/O across both disks, giving better overall performance.

  • Change the database auto grow feature to a higher value from its default of 1MB. Growing a database can be I/O intensive and you want to avoid it when possible, especially during business hours. I usually use a value of 256MB so this happens less frequently. But if your database grows faster or slower, this value can be adjusted higher or lower. Another option is to turn off auto grow and schedule it to run only when needed and during off hours. However this requires monitoring and if you forget, the database will fill up and CRM will cease to function normally or not at all.

Rob jas 1

  • For the organizations strictly used for development (non-production) purposes, consider changing the recovery model to Simple. This won’t keep a history of transactions and avoid having the transaction log grow and fill up the entire disk. Since data is constantly flowing in and being deleted to re-test the log can fill up fast. For most development environments, usually daily full or differential backups are sufficient for most organizations (unless there is a specific reason not to, for example, to match production).

Rob jas 2

Maintenance Tips Checklist

  • Make sure regular backups are being done. This sounds pretty basic, but after installing CRM, it’s possible that no one actually setup a daily backup plan. You can check this by right clicking on the database and choose Reports -> Standard Reports -> Backup and Restore Events.

  • Defragment or rebuild the indexes on a regular basis. Over time as data is added, updated, and deleted from CRM this will cause the underlying indexes to become fragmented over time. This can cause performance issues and only get worse over time. You can see if your indexes need to be maintained by right clicking on the database and choose Reports -> Standard Reports -> Index Physical Statistics. This will generate a report listing every index in the database. The column on the right will display a recommendation if the index needs to be rebuilt or reorganized. If you see a lot of these, it’s time to defragment those indexes.

  • Make sure you run CHECKDB against the database on a regular basis. This will check the consistency of the database and find any problems that need to be looked at. It’s better to find any problems early rather later after it grows to the point where the database won’t even start anymore. In that case, you may have to restore from the last backup. Note: only non-data loss CHECKDB operations are supported by CRM.

  • Check the size of your database files and the amount of disk space you have left on a regular interval. I have seen many cases where a database continues to grow until it fills up the entire disk and cannot grow anymore. Resolving this issue can be time consuming and your system will be down the entire time, so better to catch early and fix.

  • Look for any table growing out of control in size, it may indicate a more serious problem in your CRM. It’s common to see some tables, like AuditBase and Activity[Pointer/Party]Base to grow large. But if you see the contact table growing to a million records and you think you should only have 50,000 contacts, it may indicate that many duplicates have made their way into the system and something that should be investigated as to how they got in there and how to clean them up. To get a report of large tables, right click the database and choose Reports -> Standard Reports -> Disk Usage By Top Tables.

Hopefully these tips will help keep your CRM database in top condition and perform at its best. If I get any feedback below to dive into specific topics in this post in more detail, I might write that up as a separate post later.

Topics: Microsoft Dynamics 365