Connection Timeout - Need Help

Hello,

My app uses sql express on the client work station and has been installed by our application installer as a part of the prerequisites. The sql express install in standard and we've not changed any settings or brought in any tweaks.

All is well in our dev center. However, some of our clients are experiencing a problem when the app starts up. After the PC has been restarted, running the application causes it to crash with a connection time out. However, running it a second time will work with absolutely no issues.

Overall - after a restart, the first sql express connection will time out. I had a post on this earlier here -> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=93279&SiteID=1

MS Lee had suggested -- > "As for the first connection failing and subsequent connections succeeding, one potential cause is the cached last connect information. If you're connecting to a named instance and not specifying a port, then the client will determine the port by querying SQL Browser on the server"

I'm not using a remote sql express. It is running on the local machine. My connection string looks like -->

"Integrated Security=True;User Instance=True;Data Source=.\SQLExpress;AttachDBFilename=<%USERS_LOCAL_FOLDER%>;"

What may be the problem ? Any help/advise will be great. I'll need to sort this out ASAP.

Thanks,

Avinash

System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)

at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected)

at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()

at System.Data.SqlClient.TdsParserStateObject.ReadByte()

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.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)

at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at ***.**.Base.DataServices.DataFactory.SQLProvider.GetConnection(String connectionString) in c:\DevNet\SCAH\***.**.Base.DataServices\DataFactory\SQLProvider.cs:line 30

at ***.**.Base.DataServices.DalContext.OpenConnection() in c:\DevNet\SCAH\***.**.Base.DataServices\DALContext.cs:line 166

at ***.**.Base.DataServices.DataManager.GetTable(String sql, String tableName, DataSet dataSet) in c:\DevNet\SCAH\***.**.Base.DataServices\DataManager.cs:line 93

Hi Avinash,

I'd recommend setting the Connect Timeout or Connection Timeout values in the connection string to a higher value. I believe the default is 60 seconds.

The most likely cause of the problem you area seeing is a result of how User Instances work. User Instances launch a new process of SQL Server that runs in the context of the application user. In order to do this, we need to setup some directories in the user's profile and copy some files into that directory. This takes some time to do, and it can cause a timeout to occur the first time the User Instance is created.

That work only needs to be done once, so subsequent connection succed as you've found.

Regards,

Mike Wachal
SQL Express team

Mark the best posts as Answers!

|||

Hello Mike W,

Many thanks for your response. I'll go ahead and try that out.

Question - will the folders/files need to be set up each time the sql process is started ? Won't it use ones created from a previous run ?

Also - some of the clients experiencing this problem are running very modern laptops with generous amounts of RAM and Hard Disk space being available. They were found to be rather light and no heavy apps/processes were running when this occurred. In that light - I suppose 60 Seconds is reasonable time to get started. Thoughts ?

But, I'll go ahead and try it - we'll see how it goes with our next build.

Thanks again.

Regards,

Avinash

|||

Hi Avinash,

The files and folders only need to be created once per user. The second time you try to connect, the folders and files are there and those are used. This is why you're seeing the behavior of a timeout the first time, but success the second (and subsequent) times.

It doesn't really have much to do with how modern your computer is, it has to do with HD access time and how long it takes to copy a file. Simply put, the default timeout is too short to accomplish both the file copy and the connection. I believe the default timeout is 60 seconds, so I'd go with something longer. A little trial and error should get you the right time.

Regards,

Mike

Mark the best posts as Answers!

|||

One other possibility I can think of is: You have some network issue with slow DNS.

Every time you make a connection, we need to do a couple of DNS resolve (in some cases both directions, IP <--> DNS). The connection may timeout if you DNS is slow. The second, windows will pick up the DNS caches and you can get connected much faster. If you know a machine that can expecience this first-time-timeout issue for sure, you can try to ping the name of the server machine before you connect the server. If the issue is gone in this case, you know the reason is slow DNS.

Another way to verify, run "ipconfig /flushdns" from command window and try connect to the server afterwards. See whether you see the timeout issue this time.

|||

This may be a dumb question. But I'll go ahead and ask anyway.

Is this valid in the case of SQL Express and particularly when both the client application(C# Smart Client) accessing the express database and SQL Express itself are running on the same machine ? Cause this is the mode in which we are seeing this issue.

Will a DNS resolve be needed when we use ".\SQLExpress" in the connect string ?

Thanks,

Avinash

|||

I would guess that DNS has nothing to do with accessing a local instance of SQL Express. As I said in an earlier post, the most likely issue here is the delay caused by the file copy that is required the first time (and only the first time) you create a User Instance for a given user.

Has adjusting the timeout eliminated the problem?

Regards,

Mike

|||

Hello Mike,

The last build has just been shipped out. I've set the time out to 120 seconds. Should hear from our clients in a few days.

Will let you know how it goes.

Thanks for all the help,

Regards,

Avinash

|||

Hello Mike,

Just to let you know that increasing the connection time out has indeed solved the problem.

Thanks,

Avinash