Rename company name in NAV2009R2 with SQL2008R2 is VERY slow

When I rename a company name in Dynamics NAV 2009 R2 (v6.00.33319) it takes around 12 hours.

The database is running on a fully patched SQL Server 2008 R2.

Database specifications:

1.5 gb, recoverymode:simple, securitymodel:standard,
backup of log files:every 15 minutes (log size: 535mb)
full backup: every 24 hours

I’m the only user in the database.

The server has 16gb of memory and two Xeon Quad Core processors @ 3.16Ghz
Hard disks: OS: 2x 72gb15K R1 / Logs 2x 72gb15K R1 / Data 4x 72gb15K R0+1

I tried renaming using the client on a i7 client and directly on the server. It makes no difference.

Anybody got an idea how to solve this one?

You didn’t say how big is your database. Also, if you do this from your computer or if you connect directly to the SQL Server and run the NAV client from there. If your database is a couple of gigs then 12 hours seems excessive, but if it is 750GB maybe not so much. It might be that there is some activity on the database while you are doing the company rename. You have to completely eliminate all traffic while that is going on. I would personally set the database to single user mode before renaming a company.

You can’t take transaction log backups when the database is in simple recovery model by the way, so that part does not seem right.

Thanks for your reply.

I am sure there is not further traffic on the server and the database size is 1.5 gb.

I changed the database to simple recovery, because it was adviced elswhere with renaming issues.

I tried putting the database in single user mode, but it still takes around 12 hours.

When you changed the recovery model to simple, did you also disable the job that takes transaction log backups? A company rename in a 1.5 GB database on my laptop completes in minutes at worst, on a server with those specs it should be really quick. There has to be other traffic on that server.

You did not answer my question how you connect. This type of thing is best done running the NAV client straight on the SQL Server.

This is way to long. There must be something wrong with the server, disk full or something.

I tried it on a PC and directly on the server. It doesn’t make a lot of difference. On a PC it takes a couple of hours more. Around 16 hours.

I tried it on two different servers with plenty of space. When I create a Cronus test database on these servers and rename the company it takes 3 minutes (even from a PC and not directly on the server). I guess there must be something wrong inside the database.

The majority of time in a rename is involved in the drop and create of the indexed views. This gets me thinking that someone has gone crazy with creating all kinds of SumIndex fields. Or there’s an issue in that area. Or with the keys.

First check babrowns post. If that is not the issue, then look for some strange lookup fields (these can be nearly impossible to find so you probably will need to export objects to text).

Look for a flow field that looks up to a field that is a secondary key but is not part of the primary key, this is a long known bug in Navision.

Next look for flowfields that look up to the company table, also look to see if you have added fields and keys to the company table and what they link to.

Additionally check the Object Cache of the Client (although with a 1.5 GB DB it should not be important).

My Experience with a 220GB DB: Renaming a company with 140 GB

16MB Object Cache: 12+ hours
32MB Object Cache: 12+ hours
100MB Object Cache: 1+ hour
200MB Object Cache: 0.5 hours
500MB Object Cache: 0.5 hours

Thanks for helping out everyone. I forwarded the possible solutions to the developers.

For now changing the Object Cache in the client to 200MB results in a company rename of 40 minutes.

Thanks again!

Thanks for the follow up, I would have never thought object cache is such a big factor

Thanks Frank very handy thing to know.