Transaction Log Too Big

ovicash

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

xrivo

Try to use Simple Recovery Model on Navision Database..

Michael_Schumacher

Hi Ovicash,
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).

bbrown

The recovery model used in SQL will have no effect on the growth (or lack of) of the transaction log during a Navision restore. The Navision restore is executed has a single transaction. SQL will never truncated the transaction log in the middle of a transaction.

The recovery model 'Simple' used to be called 'truncate on checkpoint' in SQL 7.0. This actually was a better description.

Michael_Schumacher

bbrown wrote:

The recovery model used in SQL will have no effect on the growth (or lack of) of the transaction log during a Navision restore.

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

ovicash

It was just a curiosity. Our maintanace plan is good I say (automated sql backups).
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 :?:

Michael_Schumacher

@Ovicash
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?

ovicash

Michael Schumacher wrote:

@Ovicash
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?

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

Michael_Schumacher

ovicash wrote:

Michael Schumacher wrote:

@Ovicash
How big is your Database?

you didn't answer my first quesion [-X ]

ovicash wrote:

ps: Shumi....u suck this year in F1

not me, but Ferrari and Bridgestone. O:)
And the other Schumi does his best...

DenSter

Open your Enterprise Manager, and click on your Navision database name. Then, on the view menu, click 'taskpad'. You should now see a visual representation of the database files, in the 'space allocated' part of the screen. It might be that the transaction log file itself is 15GB, but it's not all used. In fact I am willing to bet that if your recovery model is simple, that most of the trans log is not used.

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.

Michael_Schumacher

DenSter wrote:

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.

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 ;-)

ovicash

Michael Schumacher wrote:

ovicash wrote:

Michael Schumacher wrote:

@Ovicash
How big is your Database?

you didn't answer my first quesion [-X ]


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 ...

backup log database with truncate_only
dbcc shrinkfile (database_Log, 100)

bbrown

My first question is why are you concerned with a 15 GB transaction log file?

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.

ovicash

bbrown wrote:

My first question is why are you concerned with a 15 GB transaction log file?

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

johanna

bbrown wrote:

My first question is why are you concerned with a 15 GB transaction log file?

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.

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