Sage CRM Database Maintenance

By | June 26, 2014

Database is the most important part of any application. Being business software, Sage CRM handles very delicate data and it is necessary to maintain this data properly in order to insure integrity and improved system performance.
New Stuff: Confirm if your Sage CRM system has actually expired
There are many ways to enhance the performance of Sage CRM application’s Database but listing out few of them as we came across while dealing with our clients.
1.Reduce size of Escalation Table :
If you are using Escalation functionality over the years then there is more chance to contain large number of data in this table. If you notice carefully, you will see escalations records which have been already triggered from the system. So it’s better to delete these records which are not in use.
2.Reduce size of TableScriptsLogs Table :
As name suggest this table contains Logs for Table Script which we have configured on any entity. If this logs are not useful, straightway delete such records as it will definitely help you out to reduce size of the table and ultimately to reduce size of the database.
3. Database Archival :
Archival is one of the best options to optimize your database. As we know tables like Company, Person, Cases, Communications contain large number of data.  Refer below few steps to perform database archival.
A. Create another staging database with the same table structure mentioned in Sage CRM database.
B. Write store procedures to migrate data from production database to staging database.
C. Check and cross verify data which you have migrated in staging database.
D. Delete data from the table of production database.
E. If archived data is required in system, new tabs can be added in order to search in archived data whenever required.
4.Remove Unused Indexes :
Customized Sage CRM database may have lot of unused indexes created on standard entities. Sometimes unusual indexing can also slow down the performance of the database. We can check and remove unused indexes. Usually if no database rocket science is implemented it’s always better to keep the indexes that come out of the box J.
5.Remove Unused Tables :
Now when we use word customization it is possible that your sage CRM database contains tables which are not in use for your current CRM. Deleting such tables will definitely help you to improve database performance.
6. Add Necessary Indexes :
You need to check and identify most often used tables in your database. You can also go to more detailed level and find most often fired queries. We must design indexes based on what fields are used in where clause and order by clauses. Execution plan in SQL queries also helps in this analysis.
Also Read:
1) Few points to Enhance Performance of your SageCRM
2) Steps to be remembered while moving Sage CRM database from one server to another
3) External database connectivity
4) Cannot open database requested by the login. The login failed.
5) How to identify and backup correct Sage CRM Database