Renaming a Company on NAV 2009/Windows 2008/SQL 2008

jwilder

I have restored a copy of our live database on a test server that has Windows 2008 and SQL Server 2008. Then I have a report that renames our 3 companies, here's the code:

Company.GET('SWKLive');
Company.RENAME(NewTestCompany);
Company.GET('SWK Realty');
Company.RENAME(NewTestRealty);
Company.GET('SWK - Consolidated');
Company.RENAME(NewTestCons);

I have been using this report for years without any problems.

When the rename starts it runs for about 30 seconds and then freezes. I have let it sit for almost 2 days (thinking that the rename has to rename all the tables which in turn has to rebuild the indexed views) but still nothing.

I happened across something that seems to remedy the problem (I have reproduced this problem + semi fix twice now). If I go to task manager, end task and attempt to end the task on the NAV Client it says that the client is not responding. Then the End Task screen pops which I then hit Cancel. As soon as I do that NAV starts renaming the objects again and finishes a few minutes later.

Has anyone else run into problems with renaming in NAV 2009, Windows Server 2008 and SQL Server 2008? By the way our database is 120 gb and rename typically takes a few hours for us.

kriki

[Topic moved from 'NAV 2009' forum to 'NAV/Navision' forum]

ara3n

Have you tried to do it manually instead of doing it through code?

DenSter

Renaming a company should definately not take hours. I would try putting a COMMIT in after each rename.

jwilder

I have not done it manually, I will try it.

Renaming a company can be a fairly lengthy process becasue it needs to rebuild all the indexed views. But it would not normally take more than 45 minutes for our database.

DenSter

jwilder wrote:

rename typically takes a few hours for us.

I thought you meant per company, but that must be for all of them? Even 45 minutes per company on a production server machine sounds like a bit much to me.

Try putting COMMIT between each rename, I'm thinking maybe you are having a memory/paging issue. It commits the transaction to the database and releases memory.

jwilder

I guess it depends on the database. 2 of the Companies take 3 minutes (barely any data). Our main company is 120 gb with 40 million g/l records so you can see that creating the the index views on that one table could take a little while (ILE and Value Entry are similar).

DenSter

I've seen (with my own eyes) a 600GB company rename take less than 5 minutes.

bbrown

DenSter wrote:

I've seen (with my own eyes) a 600GB company rename take less than 5 minutes.

>= 5.0 SP1 :?:

The rebuilding of the indexed views can take some time. 45 minutes is not "out of line" for a DB of this size (maybe a bit high). I do a similar process on a ~200 GB database (85 million GL Entry and 45 Million Value Entry records) with a manual rename of 1 company that takes about 1 hour. I have found that the variations can have a lot to do with where the data is (what tables) and whether the larger tables also have a lot of indexed views.

One thing that is critical is having enough log file space so it does not need to expand during the rename. The commit after each company is a good idea as it would free up the log space.

Of course, SIFT tuning can have an impact. If a SIFT (Indexed View) is not being maintained then it doesn't need to be rebuilt.

DenSter

bbrown wrote:

>= 5.0 SP1 :?:

No it was with regular SIFT. Does building the indexed views take longer than building the SIFT tables? Have you tried turning on paralellism before renaming the company?

bbrown

DenSter wrote:

bbrown wrote:

>= 5.0 SP1 :?:

No it was with regular SIFT. Does building the indexed views take longer than building the SIFT tables? Have you tried turning on paralellism before renaming the company?

The database I mentioned took <2 minutes to rename prior to upgrading to SP1. I would think that the indexed views rebuild in less time because there are fewer of them compared to SIFT tables.

DenSter

Holy cow from 2 minutes to an hour? really? :shock:

bbrown

DenSter wrote:

Holy cow from 2 minutes to an hour? really? :shock:

A rename, on versions prior to VSIFT, didn't have to rebuild anything. It was just a rename of all the company-related tables. With VSIFT the indexed views need to be dropped and recreated due to the schema-binding. Tables with lots of records and/or lots of SumIndexes can increase this time.

DenSter

bbrown wrote:

A rename, on versions prior to VSIFT, didn't have to rebuild anything. It was just a rename of all the company-related tables. With VSIFT the indexed views need to be dropped and recreated due to the schema-binding. Tables with lots of records and/or lots of SumIndexes can increase this time.

I knew that a rename prior to VSIFT was just an object rename, but I am shocked that building the views takes that long.

Have you tried turning on paralellism while doing this? Since building the views is a purely SQL Server operation it might benefit from it.

bbrown

DenSter wrote:

bbrown wrote:

A rename, on versions prior to VSIFT, didn't have to rebuild anything. It was just a rename of all the company-related tables. With VSIFT the indexed views need to be dropped and recreated due to the schema-binding. Tables with lots of records and/or lots of SumIndexes can increase this time.

I knew that a rename prior to VSIFT was just an object rename, but I am shocked that building the views takes that long.

Have you tried turning on paralellism while doing this? Since building the views is a purely SQL Server operation it might benefit from it.

Max degree of parallelism is still at the default of 0 (all processors) so it should be taking advantage of them already if it can. (whether this should be at zero is another discussion).

This is a process that is done about once every 3 or 4 months, to update a test DB, so the 1 hour rename has never been considered excessive. The client is in the process of updating system and moving to a SAN enviroment. So I'll have an oppurtunity to do some testing/experimenting prior to the new systems coming into production.

michael_j

I am having the same problem. We are using Windows Server 2008, SQL Server 2008 and Dynamics NAV 2009 SP1 on state-of-the-art hardware, but renaming a company takes anywhere from 4 to 30 hours. Unfortunately restoring to Native DB for the rename is not an option for us.

Any ideas how we can improve the performance on this? Thanks in advance.

ara3n

You can use the following solution.

http://mibuso.com/blogs/ara3n/2009/12/0 ... amics-nav/

michael_j

ara3n: Thank you for the suggestion. I will certainly give this a try.

However, I still feel that I might have some settings seriously wrong to have such long rename times. Should a company rename of so many hours be considered normal?

ara3n

Yes renaming with 5.0 sp1 and up take now longer, since with renaming it has to drop and rebuild all the views on SQL. So if your db size is large, it will take longer.

ara3n

I believe Kriki has updated my utility with new and better option. He has blogged about this.

http://mibuso.com/blogs/kriki/2010/03/2 ... l-upgrade/