SBX - Search With Button

SBX - Forum Post Title

Timeout expired during SL 7 to SL 2018CU1 database update unsuccessful

Microsoft Dynamics SL Forum

E4SL asked a question on 2 May 2019 2:28 PM
My Badges

Question Status

Suggested Answer

Hi, Running Database Maintenance (98.290.00) to update a SL 7 database (sys & app) to SL 2018CU1. The update fails with the below error in DBBBuild.log. Using SQL Server 2016. I have set the SQL Server remote query timeout to 0, but I get the same error. If you look at the log, it fails after 7 minutes. Any help is appreciated.

Start Time: 12:14:12 PM

Database Name: TRSSolomonApp
Error Code: -2
Error Text: Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
The statement has been terminated.
Statement: SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULL_DFLT_OFF ON
GO

exec ut_build_indexes '%'


End Time: 12:21:29 PM

Reply
Mark E responded on 2 May 2019 3:14 PM
My Badges
Suggested Answer

I just ran into this recently.  It seems in SQL 2016 there are some timeout issues when applications are running queries that take a while.  I tried to tweak SQL settings to address this issue (using Google searches), but none seemed to work.  Resolution (for me) was to run the SQL query directly in SQL Server Management Studio.

Reply
E4SL responded on 2 May 2019 3:27 PM
My Badges

Thanks Mark. I ran the query in SSMS and it took under 2min. I don't know what the update process does and how it works. Did it stop after this failure or continue? If it stopped, then how to we complete the update? If it continued, does the failure of this job affect future steps in the update process? Thanks.

Reply
Mark E responded on 3 May 2019 4:43 AM
My Badges

My experience has been that the update process continues, and the log only reports the parts of the job that do not complete successfully.

Reply
Carolyn Froton responded on 9 May 2019 10:25 AM
My Badges
Suggested Answer

Dear Mark

There are known issues.

1. Set the query timeout on the sql server to zero. 

Open SSMS
Right click on the SQL server name
Select Properties
on the Left menu pane click on Connections
Under "Remote Server Connections" Make sure the Remote Query Timeout = 0 , this means no timeout.

*Then, log on to the Database Maintenance (98.290.00) screen by using Windows Authentication instead of SQL Authentication 

This one is likely the cause...

2. Remove any timeout setting in the dbbuild.ini (bug 39219)

Issue:
The default dbbuild.ini contains this value: StatementTimeout=90 (see below)
This causes the database upgrade to fail with "Timeout Expired" messages when the database contains larger tables such as PJTRAN 

Open your dbbuild.ini file
 Remove or comment out the timeout setting at the top of the text file.  either or will work, support cases have proven that out.

[Settings]
REM...ConnectTimeout=90
StatementTimeout=90

Best Regards,

Jana MacDonald

Project Support Specialist-Microsoft Dynamics SL


Reply
Mark E responded on 2 May 2019 3:14 PM
My Badges
Suggested Answer

I just ran into this recently.  It seems in SQL 2016 there are some timeout issues when applications are running queries that take a while.  I tried to tweak SQL settings to address this issue (using Google searches), but none seemed to work.  Resolution (for me) was to run the SQL query directly in SQL Server Management Studio.

Reply
Carolyn Froton responded on 9 May 2019 10:25 AM
My Badges
Suggested Answer

Dear Mark

There are known issues.

1. Set the query timeout on the sql server to zero. 

Open SSMS
Right click on the SQL server name
Select Properties
on the Left menu pane click on Connections
Under "Remote Server Connections" Make sure the Remote Query Timeout = 0 , this means no timeout.

*Then, log on to the Database Maintenance (98.290.00) screen by using Windows Authentication instead of SQL Authentication 

This one is likely the cause...

2. Remove any timeout setting in the dbbuild.ini (bug 39219)

Issue:
The default dbbuild.ini contains this value: StatementTimeout=90 (see below)
This causes the database upgrade to fail with "Timeout Expired" messages when the database contains larger tables such as PJTRAN 

Open your dbbuild.ini file
 Remove or comment out the timeout setting at the top of the text file.  either or will work, support cases have proven that out.

[Settings]
REM...ConnectTimeout=90
StatementTimeout=90

Best Regards,

Jana MacDonald

Project Support Specialist-Microsoft Dynamics SL


Reply

SBX - Two Col Forum

SBX - Migrated JS