.LOG is Getting bigger

Business Central/NAV Technical Forum
21

When making a TLog backup, it is usually truncated by default - else you have to state NO_TRUNCATE (not recommended!!!)

So, once the space was released after truncation, the SQL Server will write into the TLog from the beginning, actually the SQL Server is “rolling” through the file. Here it is important, to have the TLog at an optimal size, means to have it not too big, but also to avoid frequent file expansions/log growths. Hence, the best size of the TLog depends on the transaction-volume and the frequency of TLog backups.

The TRUNCATE_ONLY option is only feasible to prepare a SHRINK of the file, as you did. This is only recommended for specific purposes, e.g. after re-indexing the whole database, but not for normal business. Once you have shrunken the file, it may be necessary to expand the TLog (automatic growth). Each growth will create a new virtual TLog - the more VTLogs you have, the worse gets your perfromance.

22

i back up navision database once a week

and if .log is getting bigger (usually navision user cannot do their transaction)

i use this script for maintain from sql server

BACKUP LOG “Database_Name” WITH TRUNCATE_ONLY

dbcc shrinkfile (“Database_Name_log”,50)

is it recommended?

23

Firstly, it depends on the maximum loss of data you are willing to accept; secondly, on the transaction volume you process; thirdly, from the db size and available disk capacity.

To have a full benefit from the SQL backup & restore features the answer to #1 is basically “no loss at all”, means to be able to restore the db until the very last committed transaction.
#2: In NAV you usually perfrom a lot of transactions, most of them could hardly be reproduced after disaster-recovery.
#3: The more backups you want to create, the more disk capacity is needed, of course. A pretty good & sufficient backup strategy could be - ignoring your specific environment - to proceed like this:

  • One Full-Backup per day; in the evening after business hours
  • A differential backup e.g. every 2 hours during business hours
  • A TLog backup every 10 Minutes (max. data-loss = 10 Minutes)

A “lower” alternative could this:

  • One Full-Backup per day; in the evening after business hours
  • A TLog backup every hour (max. data-loss = 1 hour)

As mentioned, this may not fit for your specific requirements, but I hope this points out the basic idea …

24

ok then

thank you very much for all your information Jorg… :slight_smile:

25

I also have the same problem…

my mdf is about 45Gb and log file is 102Gb.

and I notice my log is growing after re-organize/rebuild index. about 3 or 4 Gb.

my recovery is full and backup every night.

I also backup the log file every 15 minutes.

so I just wonder whether we need to let it go and keep adding the space if not enough space.

I have shrink the log file for several time ( though I know this is not a good way to reduce and it will caost the performance and defrag)

but what can I do?

susan

26

it seems to me that log is growing bigger …is it part of Navision design?

it is also in my case… my DB is 54GB and my log file is 47Gb…

I tried not to shrink the file so often cos it will create a defrag and performance prob.

unless it is necessary.