Buildmaster service intermittent loss of connectivity to local Buildmaster database

[Guest]

We have been receiving an error every few days in Buildmaster where it states that it cannot connect to the Buildmaster database and it becomes unusable.

This is strange as the service, website and database are all hosted on the same server.

Error:
Could not connect to the BuildMaster database. BuildMaster will not function property until this is resolved. The specific error message is: 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=6448; handshake=7548; [Login] initialization=0; authentication=377; [Post-Login] complete=1000;

Even stranger than this, the only way to resolve the problem is to restart the website through IIS as well as the Buildmaster service. Restarting the service alone does not cause the website to restore itself to normal functionality.

I've tried changing the protocol to use Shared Memory, TCP/IP and Named Pipes but all three exhibit the same behaviour.

We are using a full installation of SQL Server (not Express) and have tried allocating more RAM to the server, countless service and server restarts. We also used the suggestion from https://inedo.com/support/questions/1826 and cleaned up a large quantity of log entries from the database but to no avail.

We also seem to be observing intermittent SQL Exception error messages indicating a connection (not command) timeout occurred. Example:
Unhandled Exception: 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=2265; handshake=6424; [Login] initialization=0; authentication=0; [Post-Login] complete=6050; ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync() at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket() at System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer() at System.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte& value) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover) at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData) 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, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at Inedo.Data.SqlServerDataFactory.CreateConnection()
at Inedo.BuildMaster.Data.StandardDataFactory.CreateConnection()
at Inedo.Data.SqlServerDataFactory.CreateCommand(String storedProcName, IEnumerable1 parameters) at Inedo.Data.SqlServerDataFactory.ExecuteNonQuery(String storedProcName, IList1 parameters)
at Inedo.BuildMaster.Data.StoredProcedures.Configuration_GetValue.Execute()
at Inedo.BuildMaster.Windows.ServiceApplication.ServiceConfig.get_PlanExecuterThrottle()
at Inedo.TimedExecuterBase.ExecuteMethodHost(Object state)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.TimerQueueTimer.CallCallback()
at System.Threading.TimerQueueTimer.Fire()
at System.Threading.TimerQueue.FireNextTimers()
ClientConnectionId:820290e9-2609-497f-9ccd-f27d1d7ee4ba

I've set up a test script which polls the Buildmaster database every 30 seconds over a weekend and we didn't receive a single connection or command timeout.
Meanwhile the Buildmaster error logs indicate two connection timeouts and one 'Could not connect to the Buildmaster database'.

Product: BuildMaster
Version: 4.6.4

[Guest]

This is not standard behavior, and it's almost impossible that the error message would be wrong (it's just coming from Microsoft's SQL Client libraries), so I would try to correlate the error message times with event long on the SQL Server.

Some times, DBAs will proactively reboot or restart the SQL Server, so it's possible the connection is happening during that time.

[Guest]

Thanks for your prompt reply. Looking through the event logs indicates most of the time that the BM service reports SQL errors without any errors from SQL Server itself.

I do however infrequently see MSSQLSERVER reporting an error 107 (Report Server Windows Service (MSSQLSERVER) cannot connect to the report server database.)

In terms of a DBA restarting the database, this particular database is not yet formally managed by a DBA and hasn't yet been restarted by anyone but me (process up-time confirms this).

Because my raw connection poll test over the weekend didn't indicate a single connection/command failure this leads me to wonder whether there is an issue with connections being issued from the connection pool as indicated in this question response: http://stackoverflow.com/a/9413670/4876862

I've added 'Pooling=no' to the end of the BM service connection string and will see whether any errors are reported overnight.

[Guest]

Interestingly enough, disabling connection pooling for Buildmaster seems to have solved our issues. It's been five days and there hasn't been a single error in the event logs or Buildmaster itself.

In app_appSettings.config we now have our connection string setting looking like this:
<add key="Core.DbConnectionString" value="Data Source=localhost; Initial Catalog=BuildMaster; Integrated Security=SSPI;Pooling=no" />

Could this possibly be caused by a connection in Buildmaster not being disposed properly such that when it is pulled back out of the pool, it's in a broken state?