Rename company

Nav117

Our client requested an update of their test company and also to rename it. We backed up the production company and renamed it over a VPN connection. It seemed to work fine. We are trying to rename the production company back to the original name and are experiencing some seemingly odd things. It appears to be stuck in a loop (based on the messages that appear in the bottom toolbar).

It is a SQL database, but we are using the Navision backup, rename and restore functions. We have checked some previous posts which indicate that the rename could take a very long time. Ours ran overnight and still did not complete - we thought it was possibly in an endless loop and ended it. Is it likely it was still running properly - the database is about 6 Gb, the company we were trying to rename had an .fbk of about 600 Mb. The first time we renamed it took about 5 hours. The time frame seems very drastically different. We also tried it on a test machine with their DB and the rename occurred in about 5 minutes.?! Why so many differences?

If anyone has any suggestions or explanations we would appreciate it.

Thanks

kine

Which recovery model you are using on the MS SQL?

Where you are running the Navision Client? You wrote that you rename the company over VPN - it means, that you run client on your PC and over some link you are connected to their DB (what about speed of this link?)? Or you are connecting to some terminal service in their site and run client there?

RobertMo

I have very similar symptoms when trying to rename 2 companies in an SQL DB 3.60 and the size of 50GB.

We have a few months old "development" copy of DB (size 40GB) at our companies SQL server and we have tested the renaming several times and it successfully renamed one company in cca 1 hour.

When we try to rename the company at the customer DB, the renaming lasts for more then 5 hours and still not finished although their server machine should be much faster?

I would like to ask for some ideas/experiences before arranging with the customer to go for the weekend and then try to do it in hopefully less then 48 hours?
Because it also looks as “Nav117” mentioned – in the status bar it shows “Modifying records in table x…” but it looks like some tables appear more then once – so I suspect the server is somehow looping?

Does anybody know why? Or perhaps how internally SQL works on the renaming - which table order is he taking, etc.?

bbrown

I suspect that SQL is issueing an SP_Rename for each of the companies tables. Since the company rename would be executed as a single transaction, the SQL recovery model is not relevant. SQL will not truncate the transaction log in the middle of a transaction.

One thing that can impact performance of a large transaction (i.e. A company rename or a restore) is the amount of free space in the SQL transaction log and data files. If SQL must auto-expand the log during the process, it can add time.

RobertMo

Well during our test renaming, the size of the DB files acctually didn't change at all although the DB is >95% full and the size of log file (ldf) only 140MB (peanuts compared to 40GB of total size).
So obviously Transaction log is not being created?

Otherwise the dialog window in Navision shows that it renames 1 table per second. Strange thing is that the "counter" is not growing by one, but rather advancing by few times 10. When approching cca 800+ (which is the number of the tables) it goes from the start, but not exactly the same numbers.

bbrown

So obviously Transaction log is not being created?

I find it hard to believe that this is a non-logged transaction. Failure of a non-logged transaction may corrupt your database. To confirm whether or not this is a logged transaction, conduct the following simple test.

1. Set the recovery model of your Navision database to FULL. File-Database-Alter

2. Open SQL Query Analyzer and connect to the server.

3. Execute the following SQL command:


DBCC SQLPERF(LOGSPACE)

This will list each of the SQL log files on the server along with the percentage of used space. Write done the percentage for the Navision database.

4. Execute the company rename in Navision.

5. Repeat step 3 and compare the numbers, if the number is larger the second time, then this is a logged transaction.

bbrown

I would not expect the datafiles to increase in size since you are only modifying records.

The size of the database has no dirrect bearing on the size of the transaction log. The size of the transaction log is impacted by the size and number of write transactions and how often the log is truncated.