System.Data.SqlClient.SqlException: Timeout

Question

A few days ago, I noticed that my web application giving me sql an exception timed out.

I cleared a couple of stored procedures by taking more processor and restarting the SQL Server service, and my application started working as it did before and without any delay. Three hours later, I checked it again, and I could not load the page, which gave me the time to wait for the exception. I checked the CPU server, everything is in order. I have another site under the same IIS 7 , and they work without any exceptions. I restarted the SQL Server service again and my application returned to its normal state again. And I think this seems like a problem with the SQL Server database, but I'm not sure how to fix it.

So every time I get an exception, I just restart the sql service, but of course this is not the best way. Please help me solve this problem.

Here is one of the exceptions I received.

Message: An exception of type 'System.Web.HttpUnhandledException' was thrown. Source: System.Web Inner Exception: System.Data.UpdateException: An error occurred while updating records. See InnerException for details. ---> System.Data.SqlClient.SqlException: Timed out. The timeout period expired before completion or the server does not respond to requests. The application was discontinued. in System.Data.SqlClient.SqlConnection.OnError (SqlException exception, Boolean breakConnection) when System.Data.SqlClient.SqlInternalConnection.OnError (SqlException exception, Boolean breakConnection) when System.Data.SqlClient.TdsExarParnderTarPerndarTarPerndarTarPerndarTarPerndarTarPerndarTarPerndarTarPerndarTarPerndarTarPerndarTarPerndarTarPerndarTarPerndarTarPerndarTarPerndarTarStarTerStarTerStarTerStarTerStarTerStarTerStarStarThar .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) in the System.Data.SqlClient.SqlCommand.RunExecuteReader (CommandBehavior cmdBehavior, RunBlhbetchhtbund, ResultBehl, ReturnBhhh, ReturnBhh, Bbhh, Bh, Bh, Bh, ... Sq lCommand.InternalExecuteNonQuery (DbAsyncResult result, String methodName, Boolean sendToPipe) in System.Data.SqlClient.SqlCommand.ExecuteNonQuery () in System.Data.Mapping.Update.Internal.DynamicUpdateCommandTecamemeter, parameterItame, ParameterItem, parameter2, parameter, attribute2 2 identifierValues, List 1 generatedValues) in System.Data.Mapping.Update.Internal.UpdateTranslator.Update (IEntityStateManager stateManager, IEntityAdapter adapter) --- End of internal exception stack check --- in System.Data.Mapping.Update.Internal.UpdateTranslator.Update (IEntityStateManager stateManager, adapter IEntityAdapter) in System.Data.EntityClient.EntityAdapter.Update (IEntityStateManager entityCache) in System.Data.Objects.ObjectContext.SaveChanges (Boolean acceptChangesDuringSave) under System.Data.Objects. Domain.DataLayer.OtherDataLayer.UpdateHitCounter (Int32 hlistid, Int32 hcounterid) at BuyCarandSellCar.UsedCarProfilePage.HitCounter () in BuyCarandSellCar.UsedCarProfilePage.Page_Load (Sender Object, EventArgs e) in System.Web.Util.CalliHelper.EventArgFunctionCaller (IntWtr Object) .Util.CalliEventHandlerDelegateProxy.Callback (Sender object, EventArgs e) in System.Web.UI.Control.OnLoad (EventArgs is System.Web.UI.Control.LoadRecursive () in System.Web.UI.Page.ProcessRequestMain (Boolean includeStPointBagese , Boolean includeStagesAfterAsyncPoint) Stack Trace: when System.Web.UI.Page.HandleError (Exception is System.Web.UI.Page.ProcessRequestMain (Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) in System.WebPooStage.PointPeestages Boolean includeStagesAfterAsyncPoint) in System.Web.UI.Page.ProcessRequest () in System.Web.UI.Page.ProcessRequestWithNoAssert (HttpContex t context) in System.Web.UI.Page.ProcessRequest (HttpContext context) in 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 in System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute () in System.Web.HttpApplication.ExecuteStep step Iep

Answer 1

Well, for starters, this is the “UpdateHitCounter” function, which proximally causes the problem, but you don't know if this is the cause or effect. Your request exceeds the set time specified for completion.

Whenever you have a badly working database, the approach with a wide stroke that you can run on it is to connect Sql Profiler to it, and then run the index setup wizard with the results.

If you want to use a more balanced sorting approach, you can restrict the profiler to only log requests that take longer than x seconds, whatever you think. I usually start at 5 and work from there if nothing appears. Here is a tutorial on this topic.

After defining the long queries, run them in a local copy and check the execution plans. Here is a primer for this, but first look for a "table scan".

Ultimately, either your database is suboptimal, or your equipment does not match the traffic. This is almost certainly the first, and these two approaches should help you.

Answer 2

Sounds like a leaking behavior. Not knowing your overall architecture, I cannot say what the leak is, but I could offer to look at all your tables and see if something has more rows than you expect.

You can also run your sprocs manually in SQL Management Studio and see which ones take so long — you might be using an algorithm whose uptime increases with downtime.