How to limit log size?

Ramonchu

Hello.
In Nav2016 I have around 120Mb of data but the database grows and grows, I suppose by the log files. By now the database folder size is 25GB. I've started to make the backup in simple mode to avoid a several GB backup file.
Do you think this is a log/redo log problem? How can I fix the maximum size for logs and rotate them?
Thanks.

postsaurav

Hi,
1. What is the total size of your ndf and mdf files?
2. What is the size of ldf (log) file?
3. Is this after an upgrade or its a fresh implementation?

Ramonchu

Hello Postsaurav.
1.- Mdf's around 550Mb ...\MSSSQL\DATA\
2.- ldfs around 24 Gb ...\MSSSQL\DATA\ Logs files in ..\MSSQL\LOG folder are small, about 10mb, but I have a big ldf file in DATA folder with name databasename_log_......ldf
3.- Fresh installation Nav2016, after loading items, BOMS, etc.

bbrown

Your transaction log should be large enough so it does not need to auto-grow between transaction log backups. Remember that a transaction log backup is the only thing that will truncate the transaction log. A common mistake is to set a database in FULL recovery model and then schedule only database backups. This results in a log that grows out of control.

Of course the other option is to set the database to SIMPLE recovery. But be sure this change will still meet any expected recovery abilities. It's not fun a problem and discover you don't have the backups everyone thought you did.

If you are using FULL recovery and the transaction log continues to grow, there are 2 options. The first is to expand the log so it does not need to grow. The second is to schedule more frequent backups.

Auto-grow of any SQL database files (data or log) can be a performance killer and should always be avoided.

kriki

Give this link a good look and follow it up. It gets the basics right: mibuso.com/downloads/workshop-material-of-nav-techdays-2015

mdPartnerNL

kriki wrote: »

Give this link a good look and follow it up. It gets the basics right: mibuso.com/downloads/workshop-material-of-nav-techdays-2015

thx, excellent info of 209 pages