A transport-level error has occurred when receiving results from the server

-> Application logs shows below error everyday intermittently between 12:00 AM to 12:10 AM,

Timestamp: 5/17/2020 10:05:58 PM
Message: HandlingInstanceID: c190c221-3e43-4702-83fa-533122cb3c81
An exception of type ‘System.Data.SqlClient.SqlException’ occurred and was caught.
———————————————————————————-
05/18/2020 00:05:58
Type : System.Data.SqlClient.SqlException, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
Message : A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 – The semaphore timeout period has expired.)
Source : .Net SqlClient Data Provider
Help link :
Errors : System.Data.SqlClient.SqlErrorCollection
ClientConnectionId : c43b433e-4649-48f5-b0bf-05dca3d83432
Class : 20
LineNumber : 0
Number : 121
Procedure :
Server : JBDB01
State : 0
ErrorCode : -2146232060
Data : System.Collections.ListDictionaryInternal
TargetSite : Void OnError(System.Data.SqlClient.SqlException, Boolean, System.Action`1[System.Action])
HResult : -2146232060
Stack Trace : at System.Data.SqlClient.SqlConnection.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.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoLoadDataSet(IDbCommand command, DataSet dataSet, String[] tableNames)
at Microsoft.Practices.EnterpriseLibrary.Data.Database.LoadDataSet(DbCommand command, DataSet dataSet, String[] tableNames)
at Data.Export.GetNextCreatedExport()
at Business.Export.GetNextCreatedExport()
at Facade.Export.GetNextCreatedExport()
Additional Info:
MachineName : JBAPP01
TimeStamp : 5/17/2020 10:05:58 PM
FullName : Microsoft.Practices.EnterpriseLibrary.ExceptionHandling, Version=6.0.0.0, Culture=neutral, PublicKeyToken=daf167d5325e0d31
ThreadIdentity : System User
Inner Exception
—————
Type : System.ComponentModel.Win32Exception, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
Message : The semaphore timeout period has expired
Source :
Help link :
NativeErrorCode : 121
ErrorCode : -2147467259
Data : System.Collections.ListDictionaryInternal
TargetSite :
HResult : -2147467259
Stack Trace : The stack trace is unavailable.
Category: Error
Priority: 2
EventId: 100
Severity: Error
Title:Enterprise Library Exception Handling
Machine: JBAPP01
Process Id: 6868
Win32 Thread Id: 11312
Thread Name:
Extended Properties: HelpLink.ProdName – Microsoft SQL Server
HelpLink.EvtSrc – MSSQLServer
HelpLink.EvtID – 121
HelpLink.BaseHelpUrl – http://go.microsoft.com/fwlink
HelpLink.LinkId – 20476

-> The error message at first glance seems like a network issue between Application and database server OR unresponsive database server due to high resource utilization.

-> I setup a perfmon collection on the database server and also started a continuous ping on Application server JBAPP01 pinging Database Server JBDB01 continuously using below powershell command,

ping.exe -t JBDB01|Foreach{"{0} - {1}" -f (Get-Date),$_} > C:\temp\ping\JBDB01.txt

-> The same issue happened. I checked the perfmon counters on the database server. Resource utilization was normal.

-> SQL Server error log and Event Viewer logs on database server did not have any errors during the time the error happened on the application server.

-> Checked the output of the ping command on Application Server and got below details,

5/18/2020 12:05:45 AM – Reply from 192.36.67.12: bytes=32 time<1ms TTL=127
5/18/2020 12:05:46 AM – Reply from 192.36.67.12: bytes=32 time<1ms TTL=127
5/18/2020 12:05:51 AM – Request timed out.
5/18/2020 12:05:56 AM – Request timed out.
5/18/2020 12:06:01 AM – Request timed out.
5/18/2020 12:06:06 AM – Request timed out.
5/18/2020 12:06:11 AM – Request timed out.
5/18/2020 12:06:12 AM – Reply from 192.36.67.12: bytes=32 time<1ms TTL=127
5/18/2020 12:06:13 AM – Reply from 192.36.67.12: bytes=32 time<1ms TTL=127

-> From the above output it is clear that the application is experiencing connectivity errors when there is packet loss as indicated by “Request Timed Out” from above output.

-> This was taken up with Network team. They worked with Windows team and advised that the packet loss are as a result of daily Server snapshot that was performed at that time.

-> Application team updated us that the functionality that is failing every day between 12:00 AM to 12:10 AM is some sort of health monitor that checks if all components of the Application including Database server is healthy. Since the error doesn’t have any business impact, they changed the schedule to skip between 12:00 AM to 12:15 AM and this resolved the issue.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

 

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s