CRM 2011 – Cutting my CRM database down to size

I have been investigating a database growing in size 80 gigs and counting and trying to deal with server space problems.  You can see my CRM adventures this week by reading the blogs below

CRM 2011 – Should you compress an Encrypted database

CRM 2011 – Solution not importing – Timeout errors

CRM 2011/2013 – Progress of solution import

 

The problem I was investigating was the database was big and growing and what could I do about it.

 

In this particularly CRM solution the end customer wanted some functionality which would capture all the searches for certain entities e.g. contact/case.

The way we implemented this functionality was to trigger a plugin on those entities when a RetrieveMultiple event was triggered

e.g. for contact – PostContactRetrieveMultiple

I collect the search criteria and put this information into a new Entity

There was no filter on this functionality so it fires every-time a user clicks on contacts (which fires a retrieve multiple) and lots of other places and not just when the user is searching.

Probably half the data in the database is made up of these RetrieveMultiples.

 

The plan for this data is to keep 6 months worth of RetrieveMultple data.  We will create a new DB, copy the initial data.  Delete the data older than six months in the CRM database.  Going forwards we will copy the data across in data warehouse system of copying the changes with a weekly job and deleting the RetrieveMultiples which are greater than six months.

 

AsyncOperations Table

The default behavior in CRM 2011 is to keep a record of the workflow history, in CRM 2013 online this setting is turned off.  When I first used CRM 2013 I thought none of my workflows were running because I couldn’t see any records but the settings means any workflow which ran successfully doesn’t keep a log, you can easily turn this back on).  This makes sense for CRM 2013 online because space is a premium and you don’t want to spending a monthly data storage charge for loads of Workflow history records (this probably isn’t the main cause of space issues though).

The  Async table also holds information for systems jobs like duplicate detection and bulk deletes etc.

There is an interesting KB article

Performance is slow if the AsyncOperationBase table becomes too large in Microsoft Dynamics CRM

This article has a script which removes the workflow history records and you can set this up to be a weekly basis, they do add the follow caveats

• For Microsoft Dynamics CRM 2011 The SQL script in this Knowledge Base article is a one-time effort only. You can add this as a SQL job to run on a recurring nightly, weekly, or monthly basis. As your CRM runs, you have to either apply this article weekly, depending on your business needs, or apply the solution by using BULK DELETE jobs by defining a job by using the BULK DELETE wizard.
• Make sure that the AsyncOperation records for workflows and the corresponding records are deleted from theWorkflowLogBase object.

 

This article is on CRM 4 but it explains the values in the ASync table

The asyncoperationbase table stores details of system jobs (including workflows). The following table details some of the different job types that are stored in the table.

operationtype	Description
1	System Event
9	Collect SQM data
10*	Workflow Operation
12	Update Match Code
25	Generate Full Text Catalog
27	Update Contract States

Note: For entries where the operationtype is 10, there are also related entries in the workflowlogbase table (join on the asyncoperationid field). The CRM SDK lists additional operationtypes.

State Code		Status Code
3	Completed	30	Succeded
		31	Failed
		32	Canceled
2	Locked	20	In Progress
		21	Pausing
		22	Canceling

Workflow Job

You can turn off keeping the Workflow Job retention on an individual basis, this is a good option for workflows which don’t error and you are not really interested in keeping records for.

To turn on/off the Worklflow Job retention

go to the workflow

Information

 

Fields

I don’t think it would make a significant difference but I will look at removing redundant fields from Entities in CRM.  If you consider you could be storing lots of fields you don’t need for every record, if there are a lot of records e.g. contact/case then you could trim down all those unused columns for thousands of rows.

Only using the fields you need is following best practices because not only do they take up space but also can confuse users and developers.  I have been in situations where I’m not sure if I can use a field because I’m not sure if it’s being used, where it’s being set, only to find it hasn’t got any values in the field and it’s not on the form.

CRM MVP Jukka wrote a great blog on tidying up CRM 2013

Spring Cleaning CRM 2013