How can I keep my SQL Server Express connection from timing out the first time through testing?

Question

We have some automated tests for the interaction between our data access layer (C#) and the database (MS SQL). We are using SQL Express to mount an mdf, which we revert after the testing is done. It seems that the first time the tests are run on a freshly booted machine, we see timeouts, even though SQLExpress is running. The second time, they run just fine.

Query string example:

Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|\TEST_DATA.mdf; Integrated Security=True; User Instance=True

Example error:

   [nunit2] 1) Test : System.Data.SqlClient.SqlException : Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
   [nunit2]    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   [nunit2]    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   [nunit2]    at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
   [nunit2]    at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
   [nunit2]    at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected)
   [nunit2]    at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
   [nunit2]    at System.Data.SqlClient.TdsParserStateObject.ReadByte()
   [nunit2]    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   [nunit2]    at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
   [nunit2]    at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
   [nunit2]    at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
   [nunit2]    at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
   [nunit2]    at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
   [nunit2]    at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
   [nunit2]    at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
   [nunit2]    at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
   [nunit2]    at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
   [nunit2]    at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
   [nunit2]    at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   [nunit2]    at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   [nunit2]    at System.Data.SqlClient.SqlConnection.Open()
   [nunit2]    at Test()

Has anyone experienced this? Have you found a way around it?

Answer 1

Perhaps the DB is still recovering while you are hitting it the first time or auto close is set and when you hit it the first time it has to be opened by the engine. Check for this setting by execution the following, change msdb to your database name, if 1 is returned then you have it set to autoclose

SELECT DATABASEPROPERTYEX( 'msdb' , 'IsAutoClose' )

Answer 2

It looks like this problem doesn't have much of a solution other than increasing the connection timeout or simply retrying. It's simply the cost of the server spinning up the DB on a fresh system. In my testing, it occurs after a reboot, but stopping and starting the SQL service does not cause the problem. This thread on EE poses a similar question.