Upgrade to NAV 2013 Step 2 freeze issue

Freeman

Hi all.

I'm doing upgrade from Navision 5 SP1 to NAV 2013.
At the Step 2 of Data Upgrade I faced with the freezing of NAV - I run Step 2 at Friday evening and in the Monday morning I saw no any changes (please see attached screenshot).

As I find out after two days of debugging ](*,), NAV freeze due to Dimension functionality. For defining Dimension Set ID it generates new tables in the SQL:
<CompanyName>$upgrade355_Dim (contains about 7 million records)
<CompanyName>$upgrade355_DimSetID (contains about 2 houndred records)
<CompanyName>$upgrade355_TableID

When SQL script trying to update the upgrade355_Dim.DimSetID field from the upgrade355_DimSetID table, NAV freeze.

So, I done updating one by one record to see whether it freezes or not. And it not freezes! But for updating all records in the upgrade355_Dim table I will need something about 20 days of machine time. And this is the problem.
So, question is: how to optimize SQL query or change the process of transformation Dimensions?

Any idea is appreciated.

PS:
Windows 7 SP1 64bit
SQL Server 2008 R2

8Gb RAM
Intel Core i5 3.30 GHz

BardurKnudsen

This sounds more like that the SQL server hangs or is locked or something - or some very unexpected data. On our lab tests, we are able to upgrade a database with ~20 mill. dimension entries in approx. 1hr.
Are there any customizations to the dimensions solution that might cause a different behavior?

Freeman

BardurKnudsen wrote:

This sounds more like that the SQL server hangs or is locked or something - or some very unexpected data. On our lab tests, we are able to upgrade a database with ~20 mill. dimension entries in approx. 1hr.
Are there any customizations to the dimensions solution that might cause a different behavior?

Thank you for reply!

No, there are no any customizations related to the dimensions.
As I see, Data Upgrade procedure create intermediate tables (upgrade355_Dim and upgrade355_DimSetID) and data in them successfully. So, it seems there are no errors with Dimension related data.
The problem appears when SQL script generated by the Data Upgrade procedure try to update the DimSetID field in the upgrade355_Dim table from the upgrade355_DimSetID table. Such update run too slow.
Maybe, it because of different quantity of dimensions? In the database I tries to update, it is 27 dimensions.

daborg

I am seeing a very similar issue in a test database that is being upgraded for one of our customers. This particular database has about 89 million dimension ledger entries, and we are currently about 5 days into the upgrade routine now. The SQL server is churning away on the same update statement that is mentioned above.

Does anyone have any recommendations on how to speed this up? There's no way this can work for the production environment.