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


workflow retention



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



7 thoughts on “CRM 2011 – Cutting my CRM database down to size

  1. Nikesh Das July 22, 2014 / 9:17 am

    Ben, great article! It helps to know all of this information as CRM databases can grow quite extensively. As for deleting the fields, do you know of any automated way to delete fields? Deleting fields manually in a production environment can be abit risky.


    • Hosk July 22, 2014 / 9:25 am

      Anything in a production environment is risky (BACKUP BACKUP BACKUP and BACKUP ONCE MORE)

      I think I would only delete the fields using the UI because CRM does things in the background


  2. Jim Novak March 28, 2015 / 4:35 pm

    Any thoughts on attacking attachments? Drop and maybe archive attachments that are X months/years old? I know this is all dependent on the business needs but it’s something I was thinking about during a recent upgrade for a customer. Note attachments and email attachments, specifically.


    • Hosk March 30, 2015 / 10:03 am

      This is a problem I have often thought about. Attachments would soon start to take up a lot of room.

      I would definitely archive older attachments.

      I believe (from my reading on the internet) many CRM online store their attachments in Sharepoint online and often have plugins to move the attachments into Sharepoint online on creation


      • jim novak March 30, 2015 / 12:12 pm

        Cool, a process to archive and link is a nice idea. I would assume SharePoint is the obvious choice in most cases. I have customer that has SharePoint in house but because their CRM team does not have experience in SharePoint dev, they will not integrate. Instead they uses a proprietary doc storage solution that no one understands!
        My concer is performance degradation on queries vs space constraints as much as space issues. Seems that the joins to attachments adds some time, though I have not seen any solid testing and verification of why… Like query plans and profiler outputs. May have something to do with indexing or internal SQL stuff with paging. Not a SQL guru so it’s just speculation now.
        I am going to post this to pan MCS PFE and I’ll drop a note if there is any official guidance!


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s