We recently upgraded our installation of Great Plains from version 10 to Dynamics GP 2010 and ran into some difficulties with the upgrade hanging. We’re using SQL 2008 R2 as our database back end. Let me first state that I was not involved in this upgrade and it was our vendor who was working with Microsoft on resolving this issue.

According to our vendor, the Microsoft Senior Support Escalation Engineer she was working with has seen some problems with the upgrade hanging. Microsoft has narrowed the problem down to a change that was made to the way the GL history table was upgraded in the most recent service pack and he thought the cause might be related to duplicate records in the fiscal period setup table. We did not have any duplicate records, so this was not the cause of our problems. Microsoft recommended setting the MaxDOP of the SQL Server to 1 and increasing the size of the tempdb logfile to 50 GB and ensuring there was enough disk space for that to grow, if it needed to. We made those changes and the upgrade succeeded.

I know. From a DBA viewpoint, I don’t see how those changes should have any effect. Our tempdb log file was not that big on our failed upgrade attempts, but there was certainly room on the drive for it to grow that large. My log autogrow setting is 10 MB, so if it needed to grow, it should have grown to almost fill the disk before it failed, but I did not see that happening. I also fail to see how setting MaxDOP to 1 would help. (We were doing this on a test server that had no other load or databases on it.) The upgrade did take 7.5 hours to convert our 4 company databases. Our vendor said Microsoft felt this was a bit long, but if you are limiting yourself to one processor, I would expect it to take a while.

I googled this a bit and found nothing, so I’m posting this to get the information out there. I have no good explanation for why these changes worked, but they seemed to do the trick. After the upgrade, remember to change your MaxDOP setting back to what it was originally.

Update: The above was for a test server. Today, we are doing this on production, a system that has more RAM and more processors. Turns out, the issue is that we had a really big table (69 million rows) and the upgrade apparently copies that table to tempdb, manipulates the data,  then copies it back for some reason. We started the production upgrade without changing MaxDOP to 1 and it took much longer to work on this table – 12 hours and counting versus 8 in the test environment. I saw lots of CXPACKET waits, but nothing too horrible. The upgrade looked to be working on batches if 500,000 records. After 12 hours, I switched MaxDOP to 1 and it finished processing that table (although after 12 hours, it might have been reaching the end already..)