Sql-server – Timeout expired error. Is it because of a query or server settings

errorssql serversql-server-2008-r2timeouttuning

I am getting SQL Server timeout error in my production server, every hour
(~ for 5 minutes or more) during night time. This SQL server database is supporting a .NET application. I have scheduled an email job to send errors to me and I get errors with details as below.

System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: The wait operation timed out
   --- End of inner exception stack trace ---
   at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__174_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.Tasks.Task.Execute()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.<ExecuteStoreCommandsAsync>d__c.MoveNext()
   --- End of inner exception stack trace ---

I am trying to identify what is happening at that time which causes this timeout error.

  • SQL Server version: 2008 R2
  • Physical Memory: 24 GB
  • SQL server Max memory: 18 GB

Transaction log backup runs every hour for example transaction log back runs at 4:00 am and the timeout occurs at 04:01.But this behavior is not consistent and timeout doesn't occur every hour.

Result of:

SELECT * FROM sys.dm_os_wait_stats AS dows WHERE dows.wait_type = 'THREADPOOL';

 wait_type  waiting_tasks_count  wait_time_ms   max_wait_time_ms    signal_wait_time_ms
 THREADPOOL     15223          68891                1086           4

i noticed in my Job Activity Monitor that Shrink database job which runs every week failed during its last run.I don't know its implications.Any thoughts?

Best Answer

Timeout settings are a configuration of the client connection, so are either set by the client application, or via the connection string. Look at how your applications is instantiating the database connections, or perhaps in your application config files that control how those connections are created.

But increasing the timeout is just treating the symptom. Determining why the timeout occurred is a more difficult problem, and may require some detective work. Here are some avenues of investigation:

  1. Is the error always throwing on one or two particular queries? You might need to do some query tuning.
  2. Is the error always occurring at the same time each hour? Does that correspond to maintenance running overnight, perhaps in scheduled SQL agent jobs?
  3. You might run SQL trace/profiler overnight, filtered by query duration, to see if you can better identify these long-running queries.
Related Question