Hy all
I was wandering...is there a way to restore a Navision Backup without getting a huge log after restore? I mean our database will have a 15G log after a restore.
Our database in on Sql option. I can manualy shrink the log after the restore, but is there a way to avoid this?
thx
ovidiu
Best Regards
0
Comments
You do the restore with Navision?
Then you have to change Recovery model to simple before and back to full after restoring the backup. Maybe you can do this via SQL-Skript which is startet through external Functions from within Navision, but with this I can't help much because I never tried it.
The better way would be, let SQL Server make the backups through maintenance plan and restore those if needed. and take Navision Backups only if there is no other way.
When you restore a SQL-Backup the logs won't grow.
be sure to have transaction log saved regularly when using full recovery model, or it will grow endless (until there is no more byte available on Harddisk).
Michael Schumacher
oh, what day is it? Backup day. OK, let's dump all data to /dev/nul ;-)
The recovery model 'Simple' used to be called 'truncate on checkpoint' in SQL 7.0. This actually was a better description.
I also never registered a growth but i never took care of that ;-)
But I couldn't really believe this, that's why I added the last sentence.
It' s more likely that he failed to run maintenance plan on transaction logs... a very often taken error at our customer's
Michael Schumacher
oh, what day is it? Backup day. OK, let's dump all data to /dev/nul ;-)
The incovenince of Sql backups is that they are a little bigger and you can not backup a single company. If you have 10 companys and only one is needed?
Anyway I tested that thing with simple recovery. It doesn't work(or I misseed smth). I restored the same backup on 2 diferent databasses, one with simple recovery and the other with full. The .ldf file was the same in both casses :?:
Best Regards
How big is your Database?
normally we suggest to set the Transactionlog to 25 - 50% of the Database size....
and we never get problems as long as maintenance plan is working good.
Do you always get a log of 15G after each restore?
Michael Schumacher
oh, what day is it? Backup day. OK, let's dump all data to /dev/nul ;-)
I don't konw:) I only tried once , a Nav restore. Let's hope I will never have to really use a restore.
ps: Shumi....u suck this year in F1
Best Regards
not me, but Ferrari and Bridgestone. O:)
And the other Schumi does his best...
Michael Schumacher
oh, what day is it? Backup day. OK, let's dump all data to /dev/nul ;-)
You can recover the 'empty space' by shrinking the log file. Move your mouse over the little arrow-down triangle right to the left of the 'Space Allocated' caption, and you should see the dropdown menu, with the selection 'shrink database'. When you click this, you get a dialog box, where you can select the transaction log. After shrinking the database, click on another database and then click back on your Navision database. Your trans log should now be a small fraction of what it used to be.
By the way, you can set up SQL Server to automatically shrink these files. Go into Navision, go to, File/Database/Alter, and put a checkmark in the 'auto-shrink' field. This should take care of this problem into the future.
RIS Plus, LLC
But this will slow down the system, because file fragmentation of the database files will be greater each time the system has to make the files bigger to obtain more space.
The better way is to set the database size to a size which will lasts a while until the next grow has to be done. then stop server and let the files be defragmented. after you restart the server performance will be much better if the files were very fragmented.
btw. ovicash seems to know how to shrink a file if you look at his first posting ;-)
Michael Schumacher
oh, what day is it? Backup day. OK, let's dump all data to /dev/nul ;-)
sorry..our database is 6 G..the database I tested in both ways is smaller but I "got the picture"
I'm a sql fan
Best Regards
A few comments:
AutoGrow and AutoShrink are major performance killers.
Never use AutoShrink. You have no control over when the shrink occurs. SQL just contines to check the files in a round robin fashion and when it find one to shrink it starts. This can result in the system shrinking a file then having to grow it to add data. As mentioned earlier, this can lead to file frgmentation (another performance killer).
Rely on AutoGrow only as a safety net. You should watch the database usage and manually grow the files as needed. This includes the transaction log file. AutoGrow is only triggered when the database file reaches 100% capacity. All database writes are suspended during a AutoGrow session.
We don't have a lot of free space to spare. It was just a curiosity anyway. We make sql backups.
thx 4 your answers. they helped
Best Regards
Hi bbrown,
To increase performance, I have increase the size of mdf, ndf, ldf file in the Alter Database NAV. This things make the physics file of mdf, ndf, ldf are increased too as size setting of mdf, ndf, ldf in the Alter Database NAV. Is it normal? Do you mean database usage is database used in the Database Information NAV? If increase size setting of mdf, ndf, ldf make the physics file of mdf, ndf, ldf are increased too, does the shrink database is still usefull? Please help me.. Thank you
Johanna