System.Data.SqlClient.SqlException (0x80131904): Timeout expired. Three different cases

Question

I've started to encounter timeout errors, but not sure how to approach them because they are different each time.

First type of timeout

System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
 ---> System.ComponentModel.Win32Exception (258): Unknown error 258
   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.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest)
   at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
   at System.Data.SqlClient.SqlInternalConnection.BeginSqlTransaction(IsolationLevel iso, String transactionName, Boolean shouldReconnect)
   at System.Data.SqlClient.SqlConnection.BeginTransaction(IsolationLevel iso, String transactionName)
   at System.Data.SqlClient.SqlConnection.BeginDbTransaction(IsolationLevel isolationLevel)
   at System.Data.Common.DbConnection.BeginDbTransactionAsync(IsolationLevel isolationLevel, CancellationToken cancellationToken)

Second type of timeout

System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
 ---> System.ComponentModel.Win32Exception (258): Unknown error 258
   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.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)
   at System.Data.SqlClient.SqlCommand.EndExecuteNonQuery(IAsyncResult asyncResult)
   at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)

Third type of timeout

System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
 ---> System.ComponentModel.Win32Exception (258): Unknown error 258
   at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__126_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location where exception was thrown ---
   at Dapper.SqlMapper.QueryAsync[T](IDbConnection cnn, Type effectiveType, CommandDefinition command) in C:\projects\dapper\Dapper\SqlMapper.Async.cs:line 419

How can I diagnose these problems? Where to look at? Why the same Timeout error happens on three different operations? On the client side, CommandTimeout-setting is this my last resort?

Also, one important detail: I happened to encounter this Timeout error even NOT under load in the TEST environment. .NET 6, MARS=false, Windows, Timeout=30. Only CommandTimeout has default value. Unfortunately, I did not manage to catch the exact stack trace.

PS. Looking at the search results, there does not seem to be THE solution to my problems. In one case, Timeout might help [Tried - helped to eliminate Connection timeouts]. In another - CommandTimeout [Alright, I might try it, but to type it EVERYWHERE I use the database? Also can it help with BeginTransaction-timeouts?]. Somewhere even lies the MARS-setting, but the problem reproduces even with MARS disabled. Linux or Windows - does not matter either, because on Windows problem are encountered too. We've thought migration to .NET 6 might help, but it also didn't help.

Have you check the connectivity with your server? It seems a network issue. — McNets, Jul 19 '22 at 08:43
@McNets what do you mean? These errors are not some kind of blackout - some queries run fine, but then some will time out. The same ones even. — Nikita Kalimov, Jul 19 '22 at 08:44
I mean, the connectivity is overall good. The app connects to the server, and it looks like some transient errors or maybe the server's threadpool is exhausted. I just don't know how to approach the diagnosis. How to check the connectivity? How to check the threadpool? How to determine for sure whether it's a client-side, server-side or a network-side issue? Is there one root cause or many? :\ — Nikita Kalimov, Jul 19 '22 at 08:47
Are you sure it's not just a poor query plan taking too long? — Charlieface, Jul 19 '22 at 09:18
@Charlieface: The first example is just a `BeginTransaction()` - so looks like a connectivity issue . edit: Of course it could be another query with a bad plan, taking everything down with it. — mikkel, Jul 19 '22 at 09:33
@Charlieface I am not sure. Could you please give some advice on how to assess the query plan? @mikkel has an interesting idea: what if some poor query takes everything down. I've searched a bit more, and found that some poor queries might hurt the server disk system or something. Involves poor indexing and/or scans. But how can I investigate that? `SQL Server > Properties > Activity Monitor?` — Nikita Kalimov, Jul 19 '22 at 09:54
Log in using [the DAC connection](https://www.sqlshack.com/sql-server-dedicated-admin-connection-dac-how-to-enable-connect-and-use/) while this is ongoing, run `exec sp_who2` which might indicate what's going on, also this blocking script https://blog.sqlauthority.com/2020/04/20/sql-server-blocking-tree-identifying-blocking-chain-using-sql-scripts/. The following script will tell you what is being executed `select * from sys.dm_exec_requests r cross apply sys.dm_exec_sql_text(r.sql_handle) st` — Charlieface, Jul 19 '22 at 10:33
Thank you all so much for the advice. I'll need some time to process it. Hopefully I'll come back with an answer. — Nikita Kalimov, Jul 19 '22 at 13:22

mikkel · Answer 1 · 2022-07-19T09:48:44.850

A link to MSDN someone having the same problem. I believe the relevant info is this:

The message as such is the standard message when the instance cannot be reached. However, error 258 is unusual in this context. And it is not an SQL Server error, it's an OS error; NET HELPMSG 258 says The wait operation timed out.

This could possibly indicate that you are arriving somewhere, but no one is actually listening. That is, the port is open but the process is busy with something else. (And that is necessarily not an SQL Server process.)

So, how's your SQL Server doing? Can't keep up with workload? Crashing intermittently? Antivirus and/or network protection software?

Troubleshooting tips

google sp_Blitz and siblings. They're free scripts that will help you find issues on your SQL Server. Also sp_whoIsActive - it's pretty good at showing blocking problems.

To find Threadpool starvation your best bet is probably wait stats.

Check the sys.dm_os_wait_stats DMV in sql server. Select it (and save), wait a bit, and select again - wait stats are accumulated, so select * from sys.dm_os_wait_stats will not give you anything usefull. sp_Blitz will help you do this btw.

If you have Query Store enabled, you can easily find top resource consuming queries, but also a somewhat useful wait stats breakdown - not sure where Threadpool waits is though.

System.Data.SqlClient.SqlException (0x80131904): Timeout expired. Three different cases

1 Answers1