locked
CRM 3.0 x32 to 4.0 x64 - Database grew tenfold RRS feed

  • Question

  • We upgraded from 3.0 to 4.0, and from SQL2000 to SQL2005x64, in a 2-step process.  First, we restored the data from SQL2000 to SQL2005 x32, ran the CRM Upgrade to 4.0 x.32, and finally restored the data onto SQL2005 x64 and CRM 4.0 x64.  Everything worked as expected (well, it wasn't as simple as I make it sound, but we got there!)  However, whereas our Organization database used to be about 300MB, it is now 3.5GB.  Just the 4.0 upgrade caused it to reach over 1GB, and the x64 upgrade caused it to double in size again.

    Any ideas why?  Is this to be expected?  Should I worry?
    Thursday, March 11, 2010 10:00 PM

Answers

  • Submitted the case to Microsoft and it is confirmed as a bug, expected to be fixed in UR11 slated for release around June 3.
    • Marked as answer by Bernardo999 Monday, May 3, 2010 8:19 PM
    Monday, May 3, 2010 8:19 PM
  • Confiring that Rollup 11 was released, we installed it, and after a couple of days, the extra Workflow records were deleted and the DB size shrank dramatically to where we expect it to be.
    • Marked as answer by Bernardo999 Wednesday, June 16, 2010 1:09 PM
    Wednesday, June 16, 2010 1:09 PM

All replies

  • check your SQL logs.  If you have full Transactional backup then the log files will grow.  You can also check the async table to see if that is the issue.  Here are a couple of links to help:

    Is your Dynamics CRM Database Growing?

    Restore & Recovery SQL 2005
    Best Regards | Twitter: edwardsdna
    Thursday, March 11, 2010 10:49 PM
  • Donna,

    Thanks for your reply.  We have it set to Bulk-Logged, like we always have, and we dump the transaction logs periodically throughout the day.  When I backup the database, however, even after dumping the transaction logs, the dump file is huge.  I have run a sp_spaceused on each table and these seem to be the culprits:


    name rows reserved data index_size unused PluginAssemblyBase 1048 1612304 KB 1609576 KB 392 KB 2336 KB WorkflowBase 1073 1319480 KB 1318656 KB 424 KB 400 KB



    If I am reading this correctly, these 2 tables are taking up almost 3GB, which would explain why the database is so large.
    Any thoughts on this?  All help is appreciated.

     




    Friday, March 12, 2010 1:52 PM
  • Sorry, submitting the reply seemed to mess up the formatting:


    name  rows  reserved  data  index_size  unused
    PluginAssemblyBase  1048  1612304 KB  1609576 KB  392 KB  2336 KB
    WorkflowBase  1073  1319480 KB  1318656 KB  424 KB 400 KB




    Friday, March 12, 2010 1:54 PM
  • This looks like yoiu've got a lot of workflow rules, or more specifically, lots of versions of workflow rules (each time you publish a workflow rule it will retain the previous version of the workflow definition).

    CRM 4.0 stores the definition of workflow rules in a very different way from how CRM 3.0 stored this information, though I'm surprised at the extent of the size increase. The doubling of size from 32 to 64 bit can be explained by the fact that these tables store compiled workflow definitions.

    Is this causing much of a problem ? To me 3.5 GB is a pretty small CRM database, and not something to worry about. If you do want to clear out some space, the first thing I'd do is check if any records in these table have deletionstatecode = 2. If so, the deletion process should clear them out
    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk
    • Marked as answer by Donna EdwardsMVP Friday, March 12, 2010 6:15 PM
    • Unmarked as answer by Bernardo999 Wednesday, June 16, 2010 1:09 PM
    Friday, March 12, 2010 4:30 PM
    Moderator
  • David,

    I appreciate the reply.  Ours is a small database and it has always been 300MB or so.  There are about 1050 records in the PluginAssemblyBase table, almost all of which have DeletionStateCode = 2.  I restarted the Asynchronous Processing Service (I saw this suggestion somewhere) but the DeletionStateCode = 2 records haven't moved.

    Is it safe to delete them?  I really only have about 4 workflows, with maybe 30 steps in total.

    I still cannot explain the huge WorkflowBase table, which has no records with DeletionStateCode = 2.

    Thanks again for your help.

    - Bernard
    Friday, March 12, 2010 10:42 PM
  • Hi Bernardo,

    I don't recommend a hard delete of the records.  It is probably important to understand why the records are not picked up by the deletion job or to understand if the service is working as expected.  Have a look at this post by Ascentium for a method to force the deletion job to run and give it a try.  http://consulting.ascentium.com/blog/crm/Post98.aspx.

    You can also have a look at this KB for more information about the async service and related tables.  http://support.microsoft.com/kb/968520

    Best Regards | Twitter: edwardsdna
    Saturday, March 13, 2010 3:56 PM
  • When I came in this morning, the records in PluginAssemblyBase with DeletionStateCode = 2 had been deleted, lopping about 1.6GB off my database.  I guess patience was the trick!  :)
    Now I just have to figure out why WorkflowBase is so huge.  It has no DeletionStateCode = 2 records.

    Thanks to all for their help.

    - Bernard
    Monday, March 15, 2010 12:44 PM
  • I haven't yet dumped the database, but I suspect it will be much smaller.  However, the WorkflowBase table is still huge.  It has no DeletionStateCode = 2 records, but it does appear to have many copies of workflow rules.  I understand that there is some history / versioning that goes on, but I have really only a handful of rules and I don't need the history.  Is there any way to purge the old records?  1.6GB is better, but for my purposes, this is huge - 300MB of data and 1.3GB of workflow?  :)
    Monday, March 15, 2010 2:44 PM
  • Are you sure it is the workflowbase or the workflowlogbase?  The workflowbase contains records of the workflows and their associated steps.  However, the workflowlogbase contains history of the workflow actions.  You can view this link for a method of cleaning up the workflowlogbase table.  the post I referenced above has a link to the KB on how to cleanup the async and workflowlogbase table.  http://support.microsoft.com/kb/968755


    Best Regards | Twitter: edwardsdna
    Monday, March 15, 2010 4:21 PM
  • It is clearly the WorkflowBase table.

    name,rows,reserved,data,index_size,unused
    WorkflowBase,1073       ,1319480 KB,1318656 KB,424 KB,400 KB

    name,rows,reserved,data,index_size,unused
    WorkflowLogBase,14003      ,8776 KB,8632 KB,64 KB,80 KB

    The database did indeed shrink to about 1.7GB when the PluginAssemblyBase records with DeletionStateCode=2 were deleted.

    I have looked at all of the WF~ tables in my CRM 3.0 database and they are all tiny.

    I am considering blowing away all of my Workflow rules, deleting the rows in the table altogether and recreating the rules from scratch, but I am a little reluctant to delete rows without a better understanding of the schema.

    My best guess is that something is awry in the 4.0 upgrade and a number of these rows should have been marked for deletion.
    Thursday, March 18, 2010 1:53 PM
  • Submitted the case to Microsoft and it is confirmed as a bug, expected to be fixed in UR11 slated for release around June 3.
    • Marked as answer by Bernardo999 Monday, May 3, 2010 8:19 PM
    Monday, May 3, 2010 8:19 PM
  • Thank you for the update.

    Regards, Donna

            Windows Live Blog

    Monday, May 3, 2010 8:44 PM
  • Confiring that Rollup 11 was released, we installed it, and after a couple of days, the extra Workflow records were deleted and the DB size shrank dramatically to where we expect it to be.
    • Marked as answer by Bernardo999 Wednesday, June 16, 2010 1:09 PM
    Wednesday, June 16, 2010 1:09 PM