Nav 2009 R2 With SQL Server 2008(SP4)

DynamicUser

Hello Friends,

We are using Nav 2009 R2 with (LS Retail). Total db size is about 350 GB with log file, we are continuously observing that performance of Nav is getting slow, mainly it takes time in posting. Can someone help me with suggestion to improve the performance?

kriki

Start with the basics if you haven't done that yet: https://mibuso.com/downloads/workshop-material-of-nav-techdays-2015

Then : adding missing indexes (don't use SQL missing index DMV's to give you good hints...) and remove not-used indexes. But you might need someone who knows how to do this.

DynamicUser

Thanks kriki, I try to shrink the log file which is about 225 GB. But everytime it shrinks only 1%. Is there any method that I can forcefully shrink it? this could be one of the reason of getting posting slow. Moreover, this log file is of no use.

kriki

The logfile is of great use!!!

It takes on all changes before they are transferred to the DB file. This is a way to protect the DB against sudden failure of the machine (crashes, no electricity,...).

But probably such a big transaction log file does not serve.

PS: you do have transaction log backups every 15 minutes to 1 hour I hope. Without those, your full recovery is pretty much useless.

Probably you also have 10% increment on the log file.

So what I would do. Probably you have somewhere a window of a few hours where you can work on the DB and server.

-no one works!!!!!
-Full backup. This backup is VERY important so be sure it is ok (a restore on a testserver could be useful to prove that before going to the next step)!!!!
-put DB to simple recovery model
-shrink the log file as much as possible.
-change the autoincrement to 64MB (in my workshop I put 50, but 64 is more logical. This is just a failsafe. It should never happen... The reason I mostly use 2 logfiles: 1 fixed in size and then an overflow logfile which I can shrink again if needed). Also grow the DB file if it is over 80% full. Make it go down to 70% full and each week or month check it and grow the DB if necessary.
-re-grow the logfile to 50GB. Should be enough to cater for index rebuilds. You do have indexrebuilds I hope... (Ola Hallengren's scripts are great for those and other things).
-put DB to full recovery model
-shut down the SQL Server engine (if nothing else is running on it and NAV is the only DB off course!). or just put the DB offline.
-Defrag the disks where the DB and TL files are (this can slow other things running on that server or other things on the same physical host/disks).
-restart the SQL server engine or put the DB back online.
-Full backup again to re-initialise the transaction log backups.

David_Singleton

DynamicUser wrote: »

Hello Friends,

We are using Nav 2009 R2 with (LS Retail). Total db size is about 350 GB with log file, we are continuously observing that performance of Nav is getting slow, mainly it takes time in posting. Can someone help me with suggestion to improve the performance?

350Gig is not big for an LS Retail database, so this is most likely a server configuration issue. How many backend users do you have working concurrently and how many store servers do you replicate?