Recently while working on a client engagement, we needed to move users around to different business units in Dynamics CRM 4.0.  No problem right?  Well we very quickly determined things weren’t going to go smoothly.  When we attempted to change a user’s business unit, it would sit for 30 seconds and throw the lovely unhelpful generic message “An error has occurred”.  Gotta love Microsoft developer’s strong attention to error messages.  A lot of users have apparently run into this with no real good resolution.  We were able to find the true cause (at least in our case), so I wanted to share it to hopefully help others struggling with this.

The cause for this error relates to SQL.  So when a you change a user’s business unit, this fires off many many SQL UPDATE statements that have to process across many tables in the CRM database.  This is to ensure all records owned by the user are changed to reflect the new business unit.  The problem comes in when these tables are very large, which causes the UPDATE commands to timeout and throw the error.  By default, the timeout values are at 30 seconds.  Microsoft typically sees these issues relating to tables with millions of records, but we saw them at 600,000 records.  Another issue is if there are not good indexes on the tables.  The AsyncOperationsBase table stores information about system jobs.  However, this table can get very bloated.  CRM currently does not perform any logic on the entries, it just blindly tries to UPDATE all of them.  Microsoft is fixing this with update rollup 17 I believe that adds logic so it will only try and run the UPDATE SQL statements on records it finds that the user owns.  You could verify this by enabling tracing, and in the logs you will see it regarding an expired timeout.

How do you fix this timeout issue?  Microsoft wrote KB article 968520 that pretty much tells you what to do.  Obviously there could be multiple causes for this error, so gather some information:

  1. What update rollup are you running?  Make sure you are running on a recent update rollup where possible. 
  2. How big is the CRM SQL database? 
  3. Are you running regular SQL maintenance (rebuilding indexes, updating statistics, etc)
  4. Did you move users from another domain?
  5. Is there a difference between HTTP and HTTPS?

Some people have found what I call workarounds by adjusting the timeout values in the registry.  I made these changes, and it just made it take 15 minutes before I got the error.  But, for those who would like to try, you can follow the steps on MS KB 918609 for OLEDBTimeout and ExtendedTimeout.  Try that and the fix below before messing with the web.config. 

Our database was not huge at 33GB, but there was room for improvement.  Just shrinking the database got it down to 18GB.  We then proceeded to follow the Microsoft article which ultimately allowed us to change a user’s business unit.  You can read it for yourself, but I’ll outline the steps.   

  1. Open SQL Mgmt Studio and click to run a new query on the CRM database, and run the command to determine how many records really are in the AyncOperationsBase table We had over 600,000 records. 
  2. Run the script to create the three indexes.
  3. Run the script to rebuild the indexes and update statistics.
  4. Run the script to update statistics with FULL SCAN.
  5. Change the Recovery Model of the CRM database from Full to Simple (don’t forget to change back after complete).
  6. STOP the Microsoft CRM Asynchronous Processing Service (start after cleanup complete).
  7. Run the main cleanup script.
  8. Shrink the CRM database (optional).
  9. Re-run the script from step 1, and the return should 0 or almost 0 records now.
  10. Try to change a user’s business unit again.  This should literally take less than 5 seconds for 1 user.

This is a one-time operation, so you should NOT need to perform this on a regular basis.