none
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired. RRS feed

  • Question

  • We are experiencing an interment exception when querying SQL Server over our VPN a WinForms application that uses typed table adapters to get and update data on a SQL Server database.  The exception below is thrown at completely random times, during costly queries, during tiny queries, when several users are accessing the database during the day, or even when it's just me at 1:00am.  The same error happens intermittently in SQL Server Management Studio.  I can't seem to come up with a method to narrow down the issue.  Would anyone know how to troubleshoot this and/or fix it?

    I've heard this may be a network socket issue and Winsock is throwing the error, but SQL Server is reporting it.

    See the end of this message for details on invoking

    just-in-time (JIT) debugging instead of this dialog box.

    ************** Exception Text **************

    System.Data.SqlClient.SqlException (0x80131904): A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.) ---> System.ComponentModel.Win32Exception (0x80004005): The semaphore timeout period has expired

       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 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.SqlDataReader.TryConsumeMetaData()

       at System.Data.SqlClient.SqlDataReader.get_MetaData()

       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)

       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)

       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)

       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

       at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

       at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)

       at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

       at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

       at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)

       at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)

       at ProjectManager.dsProjectTableAdapters.UserTableAdapter.Fill(UserDataTable dataTable, String role)


    Ryan

    Friday, May 29, 2020 8:28 PM

All replies

  • You will need to work with whomever manages the network along with ensuring on the database server that appropriate indices are created. For indices look at SQL-Server profiler and query plan in SSMS.

    Just today I had the same issue (ended up being a broken VPN for a split second), in two plus years it has happened twice were each day the same quires run about 10,000 times a day. In my case the ROI to figure out the issue is not worth the effort as jobs run with Quartz.NET written to handle incomplete jobs.

     

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, May 29, 2020 9:28 PM
    Moderator
  • Thanks Karen! I have contacted our IT guy about the issue. I also requested the VPN software customer service get involved. Unfortunately for us, it’s happening to several users a few times a day which sounds different from your comment. So we need to do something about. I’m not entirely sure why you suggest to check indices. Perhaps you’re implying the queries are running to long and the queries are timing out? If so, that’s not the case. I see this error even when requesting a byte of data and no network traffic.

    Ryan

    Friday, May 29, 2020 10:05 PM
  • Without proper indices a query cause some of the issues which is why I mentioned this. I deal solely with web applications that when a) proper indices are not in place b) queries that work but are not written well can cause issues. 

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, May 29, 2020 10:22 PM
    Moderator