Troubleshooting Timeout SqlExceptions

I have some curious behavior that I’m having trouble figuring out why is occurring. I’m seeing intermittent timeout exceptions. I’m pretty sure it’s related to volume because it’s not reproducible in our development environment. As a bandaid solution, I tried upping the sql command timeout to sixty seconds, but as I’ve found, this doesn’t seem to help. Here’s the strange part, when I check my logs on the process that is failing, here are the start and end times:

  • 09/16/2008 16:21:49
  • 09/16/2008 16:22:19

So how could it be that it’s timing out in thirty seconds when I’ve set the command timeout to sixty??

  • How Can I Detect and Bound Changes Between Row Values in a SQL Table?
  • Delete last N characters from field in a SQL Server database
  • Returning multiple tables from a stored procedure
  • SQL Server, Using Row Number
  • SQL update only when non null
  • exec failed because the name not a valid identifier?
  • Just for reference, here’s the exception being thrown:

    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.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
       at System.Data.SqlClient.SqlDataReader.get_MetaData()
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
       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.ExecuteReader()
       at SetClear.DataAccess.SqlHelper.ExecuteReader(CommandType commandType, String commandText, SqlParameter[] commandArgs)

  • Rows showing as #DELETED
  • Audit log in core and dapper
  • SQL Keeping count of occurrences
  • MS SQL 2008 - Create a copy of the database without the data
  • Connection string for SQL Server Express on remote Computer login failed error
  • Recover unsaved SQL query scripts
  • 5 Solutions collect form web for “Troubleshooting Timeout SqlExceptions”

    SQL commands time out because the query you’re using takes longer than that to execute. Execute it in Query Analyzer or Management Studio, with a representative amount of data in the database, and look at the execution plan to find out what’s slow.

    If something is taking a large percentage of the time and is described as a ‘table scan’ or ‘clustered index scan’, look at whether you can create an index that would turn that operation into a key lookup (an index seek or clustered index seek).

    This may sound stupid, but just hear me out. Check all the indexes and primary keys involved in your query. Do they exist? Are they fragmented? I’ve had a problem where, so some reason, running the script outright worked just find, but then when I did it through the application, it was slow as dirt. The reader’s basically act like cursors, so indexing is extremely important.

    It might not be this, but it’s always the first thing that I check.

    Try changing the SqlConnection’s timeout property, rather than that of the command

    Because the timeout is happening on the connection, not the command. You need to set the connection.TimeOut property

    I had this problem once, and I tracked it to some really inefficient SQL code in one of my database’s views. Someone had put a complex condition with a subquery into the ON clause for a table join, instead of into the WHERE clause where it belonged. Once I corrected this error, the problem went away.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.