Summary:
While working with the D365 F&O build server, we suddenly started to get the timeout error on the database synchronization step. Checking the logs on the azure dev ops pointed out that, db sync was not able to complete the login phase for the sql server.
This was odd, as the builds have been working fine until this point. This was a wild goose chase to check the settings of the sql server, hosts file, testing pings etc …
Build failed:
This started happening after we took 10.0.8 quality update, or it was co-incidence – we are not sure yet.
Update 2/23/2020 10:15 PM CST:
- Update a development machine (not a build) with the same updates and the auto close was false – so hopefully it was an one off. This is good !!! 🙂
Resolution:
It came down to the “Auto close” was set to “True”, after changing that to “False”, we can see that now builds are getting done successfully.
Some readings/ view on auto close feature in sql server
- https://www.itprotoday.com/worst-practice-allowing-autoclose-sql-server-databases
- https://www.brentozar.com/blitz/auto-close-enabled/
The hints pointed to to look at auto close were as:
- DBCC Check was running again and again the AxDB, which normally shoudn’t happen.
- It was seen that AxDB start event was coming in the Application logs too frequently.
Hopefully it will help other folks as well, if such error is seen in the logs.
You can do this by SSMS UI or using following command on a database:
ALTER DATABASE [AxDB] SET AUTO_CLOSE OFF;
Full error:
Build started 2/23/2020 4:06:12 PM.
Project “C:\DynamicsSDK\Metadata\SyncEngine.proj” on node 1 (default targets).
SyncEngine:
Database synchronization…
– Metadata binaries: K:\AosService\PackagesLocalDirectory
– Synchronization mode: fullall
K:\AosService\PackagesLocalDirectory\Bin\SyncEngine.exe -syncmode=fullall -metadatabinaries=K:\AosService\PackagesLocalDirectory -connect=”Data Source=BUILD-1;Initial Catalog=AxDB;Integrated Security=True;Enlist=True;Application Name=SyncEngine” -fallbacktonative=False -raiseDataEntityViewSyncNotification
System.Data.SqlClient.SqlException (0x80131904): Connection Timeout Expired. The timeout period elapsed during the post-login phase. The connection could have timed out while waiting for server to complete the login process and respond; Or it could have timed out while attempting to create multiple active connections. The duration spent while attempting to connect to this server was – [Pre-Login] initialization=3; handshake=5; [Login] initialization=0; authentication=2; [Post-Login] complete=14746; —> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
##[error]SYNCENGINE(0,0): Error Number: -2,State:0,Class:11
SYNCENGINE : error Number: -2,State:0,Class:11 [C:\DynamicsSDK\Metadata\SyncEngine.proj]
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryPolicy.<>c__DisplayClass1.b__0()
at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)
at Microsoft.Dynamics.AX.Data.Sql.SqlDataAccessManager.OpenConnection(SqlConnection conn)
at Microsoft.Dynamics.AX.Data.Sql.SqlDataAccessManager.ExecuteSql[T](SqlCommand cmd, Func`2 sqlFunc)
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Microsoft.Dynamics.AX.Data.Sql.SqlDataAccessManager.HandleException(ExceptionDispatchInfo edi, SqlExecutionInfo execInfo)
at Microsoft.Dynamics.AX.Data.Sql.SqlDataAccessManager.ExecuteSql[T](SqlCommand cmd, Func`2 sqlFunc)
at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.ConfigureSqlDatabaseSettings()
at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.RunSync()
at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.Run(String metadataDirectory, String sqlConnectionString, SyncOptions options)
ClientConnectionId:c49c708e-c380-48e9-b81a-b41cca10ad10
##[error]SYNCENGINE(0,0): Error Number: -2,State:0,Class:11
SYNCENGINE : error Number: -2,State:0,Class:11 [C:\DynamicsSDK\Metadata\SyncEngine.proj]
Inner Exception: System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
Database synchronization failed with exit code: -1.
Done Building Project “C:\DynamicsSDK\Metadata\SyncEngine.proj” (default targets) — FAILED.
Rizwan Ahmed 