Hi,
I am running Nav 4.0 SP1 on SQL2000 using Windows authentiation. Due to some integration work between Navision and another SQL database, we need to rename and restore company in a live database.
However, the rename and restore is gradually taking longer and longer hours. There are about 9 companies in the database. This morning, the rename took 3 hours and it is still running.
Can anyone advise how and where I can fine-tune Navision or SQL so that the rename and restore is within reasonable timing? Backing up the company takes about 30 mins.
Thank you.
0
Comments
It might also help to do a truncate log after the restore.
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
I done a SQL restore on another server and the RESTORE performance improve by 15mins after putting recovery model to simple, and truncating logs. The performance improve by 15 mins. No much difference with running on single-user.
However, if I restore the company to Native, and then did another backup to restore onto a new SQL database, the RESTORE and RENAME was pretty fast.
There were few tables with Data Per Company = No. Does this contribute to the performance on the RENAME and RESTORE process.
Is there anyway I can check out what the RENAME does?
I did consider upgrading to SP2 to test out the performance but will it help to improve the RENAME and RESTORE process? There were already customisations done in SP1.
For speed : the best generally is :
-put objects in a Navision DB and remove secondary keys in the big tables.
-backup a company in SQL
-restore the company in local DB
-rename in local DB
-backup a company in local DB
-restore the company in SQL
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
After each restore/rename do a full SQL Backup, which should clear up the TL. Shrinking the files won't do any good because those will be expanded again during the next restore/rename anyway, so I would leave those alone.
RIS Plus, LLC
Is it true that SQL is updating the keys after every record is renamed?
Is there a way to turn this off?
What Kriri suggested on doing the rename using Native database and then restore into SQL indeed help to reduce the time taken significantly despite the inconvenience.
Have conducted few rounds of testing and was surprised to find that renaming a newly created empty company in SQL takes just about as long. Can only conclude that the number of companies in the SQL database indeed contribute to the performance.
I guess there is not much alternative at the present moment that SQL users who encounter slow performance in rename/restore will need a Native database to speed up the rename/restore process.
So a company-rename just renames the tablenames.
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
yes I know i bumped an old topic, but i'm still stuck with this problem ...
We have about 20 companies in Navi 3.10, SQL database has 100 GB. With few less companies, renaming took a couple of hours. Adding a company by copying and renaming took 15 hours. Adding yet another company - and now we are at 25 hours that take for renaming. I'm afraid we can't afford to add another company, as we have no free timeframe for renaming process.
Restoring a company is not working anymore, too, it just closes the Navision with the company created (tables made), but no data inside?!
So my questions:
1. anyone have any tips for restoring a company, regarding the text above?
2. kriki mentioned "put objects in a Navision DB" ... how to do that? And what is supposed to be "a local DB"?
Thanks everyone
Another possibility for you would be to do a very good analysis of the tables that are not used IN ANY COMPANY.
In these tables, you can put the table-property DataPerCompany=No. In this way all companies use the SAME table (remember NO COMPANY should use them).
An example: Let' say you don't use table 6:"Customer Price Group in ANY company. But that table is created for each company => in your case 20 tables that are doing nothing but they take a lot of time to be renamed. So if you put the table-property DataPerCompany=No, there will be ONLY 1 table. Your DB diminishes with 19 tables!
Of course (and I repeat it again!) YOU HAVE TO BE SURE NO COMPANY USES THAT TABLE!
And if you consider using it in the company, you will need to put the table-property DataPerCompany back to Yes.
2. Import them into the DB
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
Thanks for the tip!