1

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?

  • This will help. – SqlWorldWide May 9 '18 at 15:19
  • 1
    I don't think giving a large connection timeout is a good solution. – sparktech May 9 '18 at 15:32
  • 1
    I refereed to that not as a solution but to understand what is causing this. – SqlWorldWide May 9 '18 at 15:35
  • @SqlWorldWide : i understand.I have noticed very low Page Life Expectancy during those time out.I am not sure if some other application is running during that time and cause a memory crunch. – sparktech May 9 '18 at 15:37
  • 1
    Well is the index rebuild done by 4 AM? – paparazzo May 9 '18 at 16:01
2

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.
|improve this answer|||||
  • i have edited the post with more info. Running SQL profile is a expensive right so i am not sure if it is a good idea. – sparktech May 9 '18 at 15:41
  • Queries are generated from the dot net application with the help of LINQ.So i have not control over queries or tuning them.The confusing part is this time out occurs during night and load is very less during night. – sparktech May 9 '18 at 15:45
  • 1
    @sparktech - load is less from users during the night; however you probably have maintenance jobs running at that time, or backups, etc... these all take resources at the server and may be the cause of the timeouts. – Max Vernon May 9 '18 at 16:00
  • @sparktech Running profiler is expensive, yes. But using Profiler to configure a trace and then running a server-side trace without Profiler, assuming the trace is designed correctly, does not have anywhere near the same impact. Same with Extended Events. See sqlperformance.com/2012/10/sql-trace/… and sqlperformance.com/2013/07/sql-trace/…. – Aaron Bertrand May 9 '18 at 16:46
  • Since i am using SQL Server 2008 R2,i don't have the option to use Extended Events.I do have a monitoring tool ,grafana which captures memory counters.I can provide the counts. – sparktech May 9 '18 at 16:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.