2

I have the following issue on my database which is used by a c# ASP.NET application:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

I've tried sp_who and sp_who2 to check the cmd's visible there, which shows there is an average connectioncount of 10 users with AWAITING COMMAND and one CHECKPOINT.

The only way I can fix this database for several days is to take the application offline and take the database offline. Afterwards I can put the database back online and it will function without problems for a few days.

This is not a solid solution because the problem keeps coming back. But I'm out of thoughts what I can try.

What are the next steps for me to find the cause of this issue?

  • What are the queries the application is sending to SQL Server? What's the connection timeout set as on the database? – LowlyDBA Feb 5 '15 at 13:44
  • There's a lot of different queries, will try to isolate them with Profiler the next time it occurs. – Rob Feb 5 '15 at 14:13
2

Sounds like a poor performing query that is crossing over the default 30 second timeout for .net connections. SQL Server will process a query for as long as it takes, but the application is deciding to stop the show after 30 seconds.

You should fire up SQL Profiler and capture long running transactions (ones that have duration > 1 second) and transactions that require more than a few thousand reads.

Once you have a few of these queries isolated, you can begin to examine them for poor performance (look at indexes, joins, etc.)

  • Or preferably use Extended Events if possible, as Profiler is deprecated. – LowlyDBA Feb 5 '15 at 14:09
  • It may be be deprecated, but it is still part of SQL 2000, 2005, 2008, 2012, 2014. It will also be part of the next release, but after that who knows. msdn.microsoft.com/en-us/library/ms181091.aspx – datagod Feb 5 '15 at 16:26
  • @JohnM, That isn't to say that ExtendedEvents isn't awesome, because it is. – datagod Feb 5 '15 at 20:09
2

Awaiting command one of the reasons can be your stored proc waiting for user input.To deal this further ,when the issue reoccurs get the spid and see dbcc inputbuffer(spid).Try to analyze the query and see where it can wait for user input and further how can be that optimized.if you can add some queries here,we can try to help.

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.