Though it's not recommended but now we need to change a global dimension in between of the Project which has around entries in lacks. As per the standard process I navigated to General Ledger Setup -> Action -> Change Global Dimension. After around 1 hr of execution I am getting this error
A connection to SQL server is no longer usable.This could be caused by one of the following reasons:
* The server has been shut down manually or because of an error.* The SQL server connection settings are not correct.* A network failure has occurred.* A hardware failure has occurred on the server or on your computer.
I did every possible way to sort it out like -
1. Adding Port in Firewall Exception
2. Increasing SQL Command timeout.
3.Giving db_datareader, db_datawriter, db_ddladmin to the service account and NT Authority\Network Service account with default schema as $ndo$navlistener.
4.Restarting NAV And SQL Services.
I too stopped all those SQL Jobs going behind and I was the single user doing this Operation still every time I landed up with the same Issue.
I too went through https://msdn.microsoft.com/en-us/library/dn951484(v=nav.90).aspx where Even ID 216 and 112 was the case which was logging up into Event Viewer and did whatever was recommended but still on the same place.
Always Thanks to DUG.
If it stops several times approximately after the same time - try to change the SQL connection timeout to some HUGE value.
It depends on NAV version / the batch job which executes, but NAV is wilful to run such DB updates in one big transaction. I don't recall what I was trying to perform when I got these same errors, but it was something very time-consuming, too. Then this approach helped me, don't know will it work for your issue. SQL server sees no activity while NAV is "thinking" and cancels client conn on timeout.
PS Don't forget to set the timeout BACK to reasonable value afterwards! (it's measured in milliseconds) update - in latest MSSQL versions units are SECONDS, set ZERO fir infinite
but here are just some ideas, suggestions.
Check for triggers in SQL Server. Maybe there are some.
Check for scheduled jobs in SQL, which block the activity.
Have you checked for this value in SQL Server? (Remote Query Timeout.)
Maybe in some of the tables there is a Flowfield which is not optimized? (I had some problems in the past with a poor filtered Flowfield.)
I have found another setting in SQL. This could be the culprit too.
just another suggestion, idea:
Today I tried to run the Adjust Cost entries Report and it took forevere (usually it takes 5-6 mins per day), after I've seen that it won't finish(without error) I tried to run it filtered for every unadjusted item one by one. The culprit was an unset Dimension in the past. I disabled mandatory posting on the affected account and it worked.
Try to disable mandatory posting of Dimensions in the General Ledger for all accounts. Not sure if it has to do with your problem but I think it's worth a shot.
Also don't thank me for your time.
The blogs of you people and this forum helped me alot to understand how Dynamics NAV works and how to develop in it.
I have to thank you.