Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Sheila Limbaga]

Dear All,

We are often having SQL Server timeout error with our journal posting.
The journal line entries are more than 100,000 posting per month. However, just recently we are having error with SQL server disconnection. Our hard disks space are still enough I still have more than 500 GB free space for individual data and logs files. My GL Entry table contains more than 6 Million rows.

Kindly advise if re-indexing and rebuilding through SQL will help to resolve the problem.
Are there any documentation link or tips for NAV 2016 database reindexing?

Thanks

Regards,
Sheila

——————————
Sheila Limbaga
Senior Application Data and Analysis
Cordlife Group Limited
Singapore
——————————

[Paul Turner]

It is a good idea to rebuild your SQL DB indexes as part of a regularly scheduled maintenance plan.  Indexes become fragmented as more data gets added to the DB, increasing both read and write times.  We use a SQL maintenance plan to rebuild our indexes every weekend.  Our GLE has close to 6 million records as well.  Here is a link about rebuilding indexes: Reorganize and Rebuild Indexes

Please note, this may not completely solve your issue.  If a user’s posting routine takes longer than the session timeout setting, I have heard that this can cause an issue.  If rebuilding the SQL indexes does not alleviate the issue you may want to look at that timeout setting.

Other things to consider could be if you are using any ISV that added custom code either to the posting routine or to something like a custom factbox that might be doing unnecessary calculations as each record is being processed.  If you are posting from a page with any factboxes, you could try hiding them one at a time to see if any of those are adding a lot of delay to the posting routine.

Hope this helps.

——————————
Paul Turner
Liberty Mountain
Sandy UT
——————————
——————————————-

[Ian Ray]

The timeout can be set in NAV Administration

This is normally only extended for schema synchronization on upgrades, but a little more time may help with your particular issue.

——————————
Ian Ray
Cypress Grove
Arcata CA
——————————
——————————————-

[Mani S]

There are few things we can do on the backend:

1. Run update statistics on those tables that get frequently updated.
2. Run Index Maintenance job that will fix the fragmented indexes.
3. Run a profiler on the backend to check for any possible user errors
4. There’s also a setting on the sql server to set the default time to 0

——————————
Mani S
Techminds Group LLC
Rochelle Park NJ
——————————
——————————————-