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: TRSSolomonAppError Code: -2Error 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
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.
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.
My experience has been that the update process continues, and the log only reports the parts of the job that do not complete successfully.
There are known issues.
1. Set the query timeout on the sql server to zero.
Open SSMSRight click on the SQL server nameSelect Propertieson the Left menu pane click on ConnectionsUnder "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.
Project Support Specialist-Microsoft Dynamics SL
Business Applications communities