Take the 2-minute tour ×

Coupled days ago I noticed that my web application giving me sql an exception timeout expired.

I cleaned up couple stored procedures taking more cpu and restarted SQL Server service and my application started work as it was before fast and without any delay. After a three four hours I checked it again and I could not load a page than it gave me the exception timeout expired. I checked server CPU is okay everything looks fine. I have some other website under the same IIS 7 and they run well without any exceptions. I restarted SQL Server service again and my application again back to normal. And I think it looks like the problem on SQL server database but I’m not sure how to troubleshoot it.

So each time when I’m getting exception I just restart sql service but of course it isn’t the best way. Please help me to resolve this issue.

Here is the one of the exception I got.

Message: Exception of type 'System.Web.HttpUnhandledException' was thrown. Source: System.Web Inner Exception:System.Data.UpdateException: An error occurred while updating the entries. See the InnerException for details. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.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.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.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at System.Data.Mapping.Update.Internal.DynamicUpdateCommand.Execute(UpdateTranslator translator, EntityConnection connection, Dictionary2 identifierValues, List1 generatedValues) at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter) --- End of inner exception stack trace --- at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter) at System.Data.EntityClient.EntityAdapter.Update(IEntityStateManager entityCache) at System.Data.Objects.ObjectContext.SaveChanges(Boolean acceptChangesDuringSave) at System.Data.Objects.ObjectContext.SaveChanges() at BCSCDomain.Domain.DataLayer.OtherDataLayer.UpdateHitCounter(Int32 hlistid, Int32 hcounterid) at BuyCarandSellCar.UsedCarProfilePage.HitCounter() at BuyCarandSellCar.UsedCarProfilePage.Page_Load(Object sender, EventArgs e) at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) Stack Trace: at System.Web.UI.Page.HandleError(Exception e) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest() at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context) at System.Web.UI.Page.ProcessRequest(HttpContext context) at ASP.usedcarlistings_profilepage_aspx.ProcessRequest(HttpContext context) in c:\Windows\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\79794658\835d6695\App_Web_kmrmpdbb.16.cs:line 0 at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

share|improve this question
add comment

2 Answers

Sounds like leaky behavior. Without knowing your general architecture, I can't say what kind of leak, but I might suggest looking at all your tables and seeing if anything seems to have way more rows than you're expecting.

You can also run your sprocs manually in SQL Management Studio, and see which ones are taking so long -- maybe you're running an algorithm whose running time increases with uptime.

share|improve this answer
What do you mean by saying "more rows than you are expecting"? –  Morny Coll Jan 18 '11 at 2:18
I meant that if you have some sort of intuitive sense of how big your tables are supposed to get, you can just do a sanity check there. When you write your stored procedures, you have some idea of how your tables relate to one another, and you should be able to predict their relative size.If you know that an insert into table A always corresponds to an insert in table B, then you might reasonably expect that A and B are the same size. If they're not, then that's a probable indicator of a bug. –  Mark Verrey Jan 21 '11 at 21:48
add comment

Well, to start off, it's the function "UpdateHitCounter" that's proximally causing the problem, but you don't know whether this is a cause or an effect. Your query is exceeding the configured time allotted to complete.

Whenever you have a poorly performing database, a broad stroke approach you can run on it is to attach Sql Profiler to it, and then execute the Index Tuning Wizard against the results.

If you want to take a more measured, triage approach, you can restrict the profiler to log only queries that take longer than x seconds to complete, whatever you think x should be. I usually start out at 5 and work down from there if nothing shows up. Here is a primer on that topic.

Once you identify the long running queries, execute them in a local copy and examine the execution plans. Here is a primer for that, but to start out look for "table scan".

Ultimately, either your database is suboptimal, or your hardware isn't up to the traffic. It's almost certainly the first, and these two approaches should get you on your way.

share|improve this answer
+1 for the "triage approach" –  Conrad Frix Jan 18 '11 at 2:21
Thank you a lot for suggestion. At least now I know where to start looking for a problem. –  Morny Coll Jan 18 '11 at 2:31
It's interesting. I removed the HitCounter Update query, and I have started getting the same errors from different queries. I didn't have any similar issues on old server. Database is the same, more memory, what else can be wrong? –  Morny Coll Jan 18 '11 at 14:58
That goes back to what I said about cause and effect...there was nothing intrinsic to the hit counter query that was causing the problem...it was just the last query left standing without a chair. To put it more directly - another query (or queries) consumed all the resources before the hit counter had a chance to complete. You'll need to use the triage approach listed above to found out what the problem queries are, and it will have to be in iterative approach, because fixing the weakest link in the chain will then expose the next link, and so on. –  Chris B. Behrens Jan 18 '11 at 15:01
As far as why it's happening now - something is different. Probably traffic. Did you change boxes because you were getting more traffic? –  Chris B. Behrens Jan 18 '11 at 15:02
show 1 more comment

Your Answer


