SubscriptionList.DataBind() leads to SQLException "Timeout expire

SubscriptionList.DataBind() leads to SQLException "Timeout expired"

Jerry Strand

Vote:

I've been tasked with maintaining a customer's old EPiServer (5.2.375.2XX) site, which was not originally developed by me or my colleagues. My main EPiServer experience is with versions 7.5+. The site uses EPiServer's subscription subsystem, and has for, over a year, worked without any known glitches. In November last year, the subscription page (where a user can choose to subscribe/unsubscribe to/from pages) stopped working, giving the following error in the logs:

2016-11-09 16:15:37,175 ERROR [12] EPiServer.Global.Global_Error - 1.2.5 Unhandled exception in ASP.NET
System.Web.HttpUnhandledException: Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlConnection.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.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
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.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at EPiServer.DataAccess.AbstractCommand.ExecuteReader(Int32 resultSetCount)
at EPiServer.DataAccess.PropertySearchDB.<>c__DisplayClass1.b__0()
at EPiServer.DataAccess.DataAccessBase.<>c__DisplayClass1`1.b__0()
at EPiServer.DataAccess.DatabaseFactory.Execute[TResult](Action`1 method)
at EPiServer.DataAccess.DataAccessBase.Execute[TResult](Action`1 action)
at EPiServer.Web.WebControls.SubscriptionList.PopulatePages(PageDataCollection pages)
at EPiServer.Web.WebControls.SubscriptionList.CreateChildControls()
at EPiServer.Web.WebControls.PageControlBase.DataBind()
at Customer.CustomerTemplates.Public.Units.Placeable.SubscriptionPage.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
--- End of inner exception 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.ProcessRequest(HttpContext context)
at ASP.customertemplates_public_pagessecured_subscriptionpage_aspx.ProcessRequest(HttpContext context)
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

I did some testing and research and it did seem like the default 30 second timeout (SqlCommand.CommandTimeout) was reached, causing the error. I was not able to find out why since nearly all of the code of interest is EPiServer's own. Also, the error did not occur in my development environment, with an identical copy of the production database. However, the wisdom of the internet suggested that a bad query plan in SQL Server could be the culprit, and one of the suggested solutions was to clear the query plan cache.
The first time the issue occurred it was eventually "solved" by stopping IIS, taking database offline/online, and then starting IIS. However, recently the subscription page stopped working again, giving a nearly identical error (see below), and the former "solution" no longer works.

2017-04-24 13:04:25,528 ERROR [54] EPiServer.Global.Global_Error - 1.2.5 Unhandled exception in ASP.NET
System.Web.HttpUnhandledException: Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlConnection.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.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
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.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at EPiServer.DataAccess.AbstractCommand.ExecuteReader(Int32 resultSetCount)
at EPiServer.DataAccess.PropertySearchDB.<>c__DisplayClass1.b__0()
at EPiServer.DataAccess.DataAccessBase.<>c__DisplayClass1`1.b__0()
at EPiServer.DataAccess.DatabaseFactory.Execute[TResult](Action`1 method)
at EPiServer.DataAccess.DataAccessBase.Execute[TResult](Action`1 action)
at EPiServer.Web.WebControls.SubscriptionList.PopulatePages(PageDataCollection pages)
at EPiServer.Web.WebControls.SubscriptionList.CreateChildControls()
at EPiServer.Web.WebControls.PageControlBase.DataBind()
at Customer.CustomerTemplates.Public.Units.Placeable.SubscriptionPage.OnLoad(EventArgs e) in C:\Sites\Customer\src\www\CustomerTemplates\Public\Units\Placeable\SubscriptionPage.ascx.cs:line 75
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
--- End of inner exception 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.ProcessRequest(HttpContext context)
at ASP.customertemplates_public_pagessecured_subscriptionpage_aspx.ProcessRequest(HttpContext context)
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

"C:\Sites\Customer\src\www\CustomerTemplates\Public\Units\Placeable\SubscriptionPage.ascx.cs:line 75" is the line after "SubscriptionList.DataBind()" is called.

When I first encountered this issue I verified the following:

That there is enough available disc space on the machine running SQL Server.
That there is no deadlock occuring.

The site is not massive (admin interface reporting roughly 24000 pages in database), so I don't really see how the amount of data could be an issue. Also, the scheduled job responsible for sending subscription e-mails finishes in ~15 seconds.

Does anyone have an idea of what could be the cause of the issue, and how to solve it?

#177956

Apr 25, 2017 9:56