As I mentioned in the last post, I’m taking you through our adventure in upgrading the existing on-premise Dynamics CRM 2011 environment to 2016 (and eventually 2016 cloud). Previously I discussed the first show-stopper error we received, “Must declare the scalar variable “@table”.” Following that resolution the import continued past the stage “Metadata xml upgrade: pass 1” but then failed at “Upgrade Indexes”.
Through the use of trace logs obtained by using the CRM diagnostic tool, we discovered that the import wizard was marking a number of indexes to be dropped and then recreated. However, as observed through a SQL Profiler deadlock trace, it was trying to drop and add indexes on the same table at the same time. As I mentioned in my previous post, our database is in excess of 1.5TB. One of the largest tables is ActivityPointerBase, and it’s also one on which many index operations were being executed by the import wizard. The result is that some of the index operations would be chosen as the deadlock victim, causing the import wizard to throw an error and exit. Also, if you restarted the import it would process the entire list again, not taking into account any that it had dropped and recreated already.
My coworker, and local wizard, Bret Unbehagen used the trace logs to determine which tables the import wizard was using to store its index operation information. He then created the query below to produce a list of indexes that it was trying to recreate as well as generate a drop statement for each of those.
select BaseTableName, i.Name, 'drop index ' + i.name + ' on ' + BaseTableName + ';' from EntityIndex i join EntityView v on (i.EntityId = v.EntityId) where i.RecreateIndex = 1 AND I.IsPrimaryKey = 0 AND I.NAME IN (SELECT NAME FROM SYS.indexes) order by name;
So, the basic workflow is 1) let the import wizard proceed until it fails at “Upgrade Indexes”, 2) run the script above against your organization database to list the indexes that it wants to rebuild, 3) use the generated drop statements to preemptively drop those indexes, 4) restart the import so that it continues were it left off (feature of 2013+).
In our experience, this allowed the import wizard to continue through the “Upgrade Indexes” section without deadlocking and proceed with the import. Hopefully it can help you achieve success as well. If you have any questions please feel free to comment. Also, if you’d like to see more from Bret his information is listed below.
Bret Unbehagen (Twitter: @dbaunbe; Web: dba.unbe.org)
Hi Mr. Fowler,
I’m a Dynamics CRM consultant working in Copenhagen and currently upgrading a CRM 2011 to a Dynamics 365 (on-premises) for one of our customers.
I’ve stumbled upon your upgrade adventure articles (very good btw), because I’m facing quite an adventure myself. Basically, the organisation import to CRM 2013 fails, and when I’ve fixed one error, another one disappears. As the database is around 1 TB data, it takes 2-3 hours to restore it before I can start the import again . I’ve lost count on how many times I’ve done this by now.
I noticed you mentioned an organization import restart-feature, available from 2013 and up. I cannot seem to find anything about it online, and furthermore, no option in the deployment manager.
May I ask how you activate such a restart? It would save me a lot of time.
Many thanks in advance,
Johanna
Hi Johanna, I’m glad you’re finding the upgrade adventure useful. I know your pain all too well, losing hours of time simply to reset the environment. The restart feature is deceptively simple. If memory serves (and it’s been a while since I performed the upgrade), you do it exactly the same way that you start any other import. In the import wizard, just point to the database on which the import failed and it will pick up where it left off.
I hope this helps. Thanks for the comment, and let me know if you have any other questions.