Open Forum

Hello everyone,

We are experiencing an issue where our SQL backups are causing our job queues to error out with the following message when they run.  We have spaced out when the job queues run to not run when our database backups run but are still encountering 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.

Try to connect again later or contact your system administrator."

Does anyone have any further recommendations to resolve this type of issue?  Would modifying settings in Microsoft Dynamics Administration to a higher value like 'Close Inactive SQL Connections' or 'SQL Command Timeout' help with this problem?  What are the recommended database settings in Nav server administration for Nav 2017 to help resolve this?

Let me know if any further information is needed and thanks for your input on this.


------------------------------
Paul Connolly
Diesel Forward
Windsor WI
------------------------------

Are you restricting SQL's memory consumption?  We had a situation where SQL was consuming so much memory it started shutting down service tiers, this caused the Job Queue to stop.  Pretty simple solution to resolve once we got the IT company to admit the issue.

------------------------------
Ben Baxter
Consultant
Accent Software, Inc.
Carmel, IN
------------------------------

Original Message:
Sent: Feb 17, 2021 04:50 PM
From: Paul Connolly
Subject: NAV 2017 job queues interrupted by SQL backups

Hello everyone,

We are experiencing an issue where our SQL backups are causing our job queues to error out with the following message when they run.  We have spaced out when the job queues run to not run when our database backups run but are still encountering 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.

Try to connect again later or contact your system administrator."

Does anyone have any further recommendations to resolve this type of issue?  Would modifying settings in Microsoft Dynamics Administration to a higher value like 'Close Inactive SQL Connections' or 'SQL Command Timeout' help with this problem?  What are the recommended database settings in Nav server administration for Nav 2017 to help resolve this?

Let me know if any further information is needed and thanks for your input on this.


------------------------------
Paul Connolly
Diesel Forward
Windsor WI
------------------------------

I have run into issues like this where one nightly service interrupts another.  I always work with my network team to schedule those overnight service processes.  Even if you optimize them they can still delay each other and cause issues.  It is best to coordinate these so they are running separately.

Hope this helps.

Sincerely,
Victor

------------------------------
Victor Diercksen
CIO
CIO/NOW Inc.
Sandy UT
------------------------------

Original Message:
Sent: Feb 17, 2021 04:50 PM
From: Paul Connolly
Subject: NAV 2017 job queues interrupted by SQL backups

Hello everyone,

We are experiencing an issue where our SQL backups are causing our job queues to error out with the following message when they run.  We have spaced out when the job queues run to not run when our database backups run but are still encountering 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.

Try to connect again later or contact your system administrator."

Does anyone have any further recommendations to resolve this type of issue?  Would modifying settings in Microsoft Dynamics Administration to a higher value like 'Close Inactive SQL Connections' or 'SQL Command Timeout' help with this problem?  What are the recommended database settings in Nav server administration for Nav 2017 to help resolve this?

Let me know if any further information is needed and thanks for your input on this.


------------------------------
Paul Connolly
Diesel Forward
Windsor WI
------------------------------

One of the jobs is eating up too much space or taking too long to finish. We faced the same situation where adjust cost was taking too long and interrupting backup thus failing.
Check on both which one is taking longer and using up server resources. We were able to resolve it by running adjust cost at different times than the backup and giving 2 hours in between.

Hope this helps.
~Meenakshi

------------------------------
Meenakshi Singh
Ruby Award Recipient
NAVUG/D365BC-Programming Committee
------------------------------
------------------------------

Original Message:
Sent: Feb 17, 2021 04:50 PM
From: Paul Connolly
Subject: NAV 2017 job queues interrupted by SQL backups

Hello everyone,

We are experiencing an issue where our SQL backups are causing our job queues to error out with the following message when they run.  We have spaced out when the job queues run to not run when our database backups run but are still encountering 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.

Try to connect again later or contact your system administrator."

Does anyone have any further recommendations to resolve this type of issue?  Would modifying settings in Microsoft Dynamics Administration to a higher value like 'Close Inactive SQL Connections' or 'SQL Command Timeout' help with this problem?  What are the recommended database settings in Nav server administration for Nav 2017 to help resolve this?

Let me know if any further information is needed and thanks for your input on this.


------------------------------
Paul Connolly
Diesel Forward
Windsor WI
------------------------------

We are on NAV 2018 and do not have any issues with any SQL job (backups, DBCC Check which takes 3 hours) causing issues with Job Queue.  That message means SQL has disconnected for some reason so check your SQL Command Timeout in the Service Tier.  Ours is set to 23:59:00.  Doesn't need to be that long but probably won't negatively effect anything either.  Check your timeout value set in SQL Server itself (Server Properties, Connections, Remote Query Timeout in seconds (ours is set to 600).

Also I can't remember if NAV 2017 is Job Queue or Task Scheduler.  When you go to the Service Tier, do you see a Task Scheduler fasttab and are you using it?  If you use Task Scheduler what are your settings for System Task End Time and System Task Start Time?

------------------------------
Jason Wilder
Senior Application Developer
Stonewall Kitchen
York ME
------------------------------

Original Message:
Sent: Feb 17, 2021 04:50 PM
From: Paul Connolly
Subject: NAV 2017 job queues interrupted by SQL backups

Hello everyone,

We are experiencing an issue where our SQL backups are causing our job queues to error out with the following message when they run.  We have spaced out when the job queues run to not run when our database backups run but are still encountering 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.

Try to connect again later or contact your system administrator."

Does anyone have any further recommendations to resolve this type of issue?  Would modifying settings in Microsoft Dynamics Administration to a higher value like 'Close Inactive SQL Connections' or 'SQL Command Timeout' help with this problem?  What are the recommended database settings in Nav server administration for Nav 2017 to help resolve this?

Let me know if any further information is needed and thanks for your input on this.


------------------------------
Paul Connolly
Diesel Forward
Windsor WI
------------------------------

Jason,

Yes we have a task scheduler fast-tab on the Nav tier, there are two fields on it.  'Enable Task Scheduler' is checked on and 'Maximum concurrent running tasks' is set to 10.  Please let me know if any of these need to be adjusted.

We will take a look at the other settings you have mentioned as well.

Thanks.

------------------------------
Paul Connolly
Diesel Forward
Windsor WI
------------------------------

Original Message:
Sent: Feb 19, 2021 09:07 AM
From: Jason Wilder
Subject: NAV 2017 job queues interrupted by SQL backups

We are on NAV 2018 and do not have any issues with any SQL job (backups, DBCC Check which takes 3 hours) causing issues with Job Queue.  That message means SQL has disconnected for some reason so check your SQL Command Timeout in the Service Tier.  Ours is set to 23:59:00.  Doesn't need to be that long but probably won't negatively effect anything either.  Check your timeout value set in SQL Server itself (Server Properties, Connections, Remote Query Timeout in seconds (ours is set to 600).

Also I can't remember if NAV 2017 is Job Queue or Task Scheduler.  When you go to the Service Tier, do you see a Task Scheduler fasttab and are you using it?  If you use Task Scheduler what are your settings for System Task End Time and System Task Start Time?

------------------------------
Jason Wilder
Senior Application Developer
Stonewall Kitchen
York ME

Original Message:
Sent: Feb 17, 2021 04:50 PM
From: Paul Connolly
Subject: NAV 2017 job queues interrupted by SQL backups

Hello everyone,

We are experiencing an issue where our SQL backups are causing our job queues to error out with the following message when they run.  We have spaced out when the job queues run to not run when our database backups run but are still encountering 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.

Try to connect again later or contact your system administrator."

Does anyone have any further recommendations to resolve this type of issue?  Would modifying settings in Microsoft Dynamics Administration to a higher value like 'Close Inactive SQL Connections' or 'SQL Command Timeout' help with this problem?  What are the recommended database settings in Nav server administration for Nav 2017 to help resolve this?

Let me know if any further information is needed and thanks for your input on this.


------------------------------
Paul Connolly
Diesel Forward
Windsor WI
------------------------------