Simple insert causes database timeout

Douglas Fittipaldi asked on February 14, 2025 19:41

I built a quick script using the code from here and it does create the page in the tree, but when I try to check out I get this error in the log:

Description:Message: Exception of type 'System.Web.HttpUnhandledException' was thrown.

Exception type: System.Web.HttpUnhandledException 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.cmsmodules_content_cmsdesk_default_aspx.ProcessRequest(HttpContext context) at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step) at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

Message: The query provider failed to execute a query represented by a LINQ expression.

Exception type: CMS.DataEngine.LinqExpressionCannotBeExecutedException Stack trace: at CMS.DataEngine.CMSQueryProvider1.ExecuteInternal(Expression expression) at CMS.DataEngine.CMSQueryProvider1.Execute[TResult](Expression expression) at CMS.DataEngine.AbstractInfoProvider3.GetInfoById(Int32 id, Boolean useHashtable) at CMS.DocumentEngine.TreeProvider.SelectSingleNode[NodeType](Int32 nodeId, String cultureCode, Boolean combineWithDefaultCulture, Boolean coupledData) at CMS.UIControls.DocumentUIHelper.GetDocumentPageUrl(UIPageURLSettings settings, Func2 getActionUrl) at CMS.UIControls.ContentUrlRetriever.GetRequestedUrlInternal(UIPageURLSettings settings, Boolean ajaxRequest) at CMS.UIControls.ContentUrlRetriever.GetRequestedUrl(String eventArgument, Boolean ajaxRequest) at CMS.UIControls.ContentUrlRetriever.OnPreInit(Object sender, EventArgs e) at System.EventHandler.Invoke(Object sender, EventArgs e) at System.Web.UI.Page.OnPreInit(EventArgs e) at CMS.UIControls.AbstractCMSPage.OnPreInit(EventArgs e) at CMS.UIControls.CMSPage.OnPreInit(EventArgs e) at CMSModules_Content_CMSDesk_Default.OnPreInit(EventArgs e) in D:\a\1\s\CMS\CMSModules\Content\CMSDesk\Default.aspx.cs:line 152 at System.Web.UI.Page.PerformPreInit() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Message:

SELECT TOP 1 * FROM CMS_Tree WHERE [NodeID] = @NodeID

Caused exception: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Exception type: System.Exception Stack trace: at CMS.DataEngine.AbstractDataConnection.HandleError(String queryText, Exception ex) at CMS.DataEngine.AbstractDataConnection.ExecuteReader(String queryText, QueryDataParameters queryParams, QueryTypeEnum queryType, CommandBehavior commandBehavior) at CMS.DataEngine.GeneralConnection.ExecuteReaderCore(QueryParameters query, CommandBehavior commandBehavior) at CMS.DataEngine.SqlExecutionHelper.ExecuteWithRetryCore[TResult](Func1 executionFunc, Int32 retryCount, Func2 exceptionRetryPredicate) at CMS.DataEngine.SqlExecutionHelper.ExecuteWithDeadlockRetryInternal[TResult](Func1 executionFunc, Int32 retryCount) at CMS.DataEngine.DataQueryBase1.GetReaderFromDBInternal(CommandBehavior commandBehavior, Boolean newConnection) at CMS.DataEngine.DataQueryBase1.GetReader(CommandBehavior commandBehavior, Boolean newConnection) at CMS.DataEngine.DataQueryBase1.GetReader(CommandBehavior commandBehavior, Boolean newConnection) at CMS.DataEngine.DataQueryBase1.d__126.MoveNext() at CMS.DataEngine.DataExtensions.As[InfoType](IEnumerable1 dataRecords, Func2 createObjectFunc) at CMS.DataEngine.ObjectQueryBase2.GetEnumerator() at CMS.DataEngine.EnumerableWrapper1.-GetEnumerator>d__4.MoveNext() at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable1 source) at System.Linq.EnumerableExecutor1.ExecuteBoxed() at CMS.DataEngine.CMSQueryProvider1.ExecuteInternal(Expression expression)

Message: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Exception type: System.Data.SqlClient.SqlException Stack trace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) 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 CMS.DataEngine.AbstractDataConnection.ExecuteReader(String queryText, QueryDataParameters queryParams, QueryTypeEnum queryType, CommandBehavior commandBehavior)

Message: The wait operation timed out

Exception type: System.ComponentModel.Win32Exception

Is there anything I'm missing? Could there be some weirdness going on with the database. I tried that KInspector but that didn't get me anywhere. Any help would be appreciated. Thanks.

kentico 13 database query

Recent Answers

Juraj Ondrus answered on February 17, 2025 12:55

What is the exact version including hotfix and where and how are you executing the code? Are you able to reproduce this ona fresh, out of the box installation? If yes, what are the steps we need to follow, please?

0 votesVote for this answer Mark as a Correct answer

Douglas Fittipaldi answered on February 18, 2025 13:51

The version we're using is v13.0.138. I'm executing the code in a separate website. This doesn't happen in a fresh installation. This is happening against a database that has been used for testing, so that's why I'm wondering if there's some kind of orphaned record that's the issue. I'm sorry if this is nothing to go on, so please let me know if you need more information.

0 votesVote for this answer Mark as a Correct answer

Douglas Fittipaldi answered on February 18, 2025 19:54

One more note. I am able to run KIspector now since SQL isn't giving me a timeout, so if you need me to run a report from that, let me know. Thanks again for helping.

0 votesVote for this answer Mark as a Correct answer

Juraj Ondrus answered on February 19, 2025 08:48

How many records are in the pages tables? And if you are using KInspector, use the document/page consistency report to see if there are any orphaned records or other inconsistencies.

0 votesVote for this answer Mark as a Correct answer

Douglas Fittipaldi answered on February 19, 2025 14:07

In both CMS_Document and CMS_Tree there are 18,422 records. The database is keeping track of records for 4 sites but it's the main one that is causing issues. I don't seem to have this issue when I try doing inserts targeting one of the test sites. As far as KInspector, when I try to run the Content Tree Consistency Analysis report, I get this error:

fail: Microsoft.AspNetCore.Server.Kestrel[13]
  Connection id "0HNAGSMVFJBQ5", Request id "0HNAGSMVFJBQ5:00000006": An unhandled exception was thrown by the application.
  System.Data.SqlClient.SqlException (0x80131904): The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.

Other reports seem to run fine. Let me know if you want a different report. Thanks again for your help.

0 votesVote for this answer Mark as a Correct answer

Juraj Ondrus answered on February 20, 2025 06:39

It seems that there are more than 2100 values being passed to the query which is not supported by the SQL. It seems that you will have to check the consistency manually. Please see the page database structure and then try to find orphaned records - e.g. nodes not assigned to a site, or missing records in the CMS_Document table or coupled data tables.
I am unable to tell what is wrong without knowing how to reproduce the issue on our side. I am sorry :-(

0 votesVote for this answer Mark as a Correct answer

Douglas Fittipaldi answered on February 20, 2025 15:39

No need to apologize. I know how something like this is tricky to diagnose. I've just issued some queries to check if there are records in CMS_Document that aren't in CMS_Tree and vice-versa but nothing came up there. I also checked the coupled tables but they all seem to have a record in CMS_Document as well. I can tell you that when I was originally trying this out I did end up deleting records manually from CMS_Document, CMS_Tree, and CMS_PageUrlPath, so I'm wondering if I missed deleting any records out of some tables. Also, since a number of the SQL timeouts I run into seem to be when querying the CMS_VersionHistory table I was wondering if there were any queries I could run against that that would reveal some kind of discrepancy. Again, thanks for helping me with this.

0 votesVote for this answer Mark as a Correct answer

Juraj Ondrus answered on February 21, 2025 05:41

How many records are in the CMS_VersionHistory? If the version length is set to a bigger number, the table can bloat rapidly - this could be part of the issue. Also, does this mean you are using workflows, versioning?
If yes, then there could be an issue when creating the first, initial (currently edited) version of the page - but maybe there are just too many records in the version history table. So, maybe doing some clean up, deleting the oldest version or setting the history length to a lower number (which automatically deletes the older versions data) could help.

0 votesVote for this answer Mark as a Correct answer

Douglas Fittipaldi answered on February 21, 2025 20:28

So here's what I found:

Version history length (minor versions): 10

Version history length (major versions): 5

Save to last version if younger than (minutes): 5

Promote to major version if older than (hours): 15

Not especially large. I also issued this query:

SELECT [DocumentID], count(*) as num FROM [CMS_VersionHistory] where NodeSiteID = 1 group by DocumentID order by num desc

When I did that I saw there were a number of Documents with a count of 20, and one at 21. Does that seem fishy to you or am I just misunderstanding something?

0 votesVote for this answer Mark as a Correct answer

Juraj Ondrus answered on February 22, 2025 05:11

Those numbers seem OK, but what is the total number of records in the history table? What if there are thousands of records from other site? Does the new page creation work when in admin UI, in the content tree?

0 votesVote for this answer Mark as a Correct answer