Manual Web Farm Error in Event Log - Missing SQL Parameters

Kenneth Gilyard asked on May 5, 2020 22:43

Hello, I am running a recently upgraded Kentico v12.0.6 MVC on an Azure Web App. I have set the MVC and CMS project to have a static Server Name in the web config. I have Web Farms set to Manual. Web Farms are healthy and named as expected. No pending tasks visible via Kentico admin. I have cleared the SQL tables manually, restarted web farm server, and restarted application.

Every 20 mins I get this error in the event log. The latest hotfix (notes found here) touches upon webfarms but I don't think it is the same issue I am experiencing.

Error in Event Log. Source:WebFarmTaskProcessor Event code:System.Exception_CMS.DataEngine

Description: Message:

-- Delete server tasks that are violating foreign key policy because of nolocks in inserting servertaks DELETE FROM [CMS_WebFarmServerTask] WHERE [ServerID] NOT IN (SELECT [ServerID] FROM [CMS_WebFarmServer]) -- Delete old tasks that were already processed DELETE TOP(@deleteTaskCount) FROM [CMS_WebFarmTask] WHERE [TaskIsAnonymous] = 0 AND [TaskID] NOT IN (SELECT [TaskID] FROM [CMS_WebFarmServerTask]) AND [TaskCreated] < @deleteOlderThan -- Return number of deleted tasks SELECT @@ROWCOUNT

Caused exception: Must declare the scalar variable "@deleteTaskCount". Incorrect syntax near the keyword 'AND'.

kentico 12 mvc azure app service

Recent Answers

Peter Mogilnitski answered on May 6, 2020 00:01 (last edited on May 6, 2020 00:02)

The query crushing because CMS_WebFarmServerTask is empty. Is there particular reason why you want to have manual web farms? Automatic mode is recommended by Kentico and I personally never had a reason to use manual. For azure automatic mode is enabled by default.

0 votesVote for this answer Mark as a Correct answer

Juraj Ondrus answered on May 6, 2020 05:46

There were bugs related to this fixed in earlier hotfix no.35:

"Web farms - Accumulation of redundant records in the 'CMS_WebFarmTask' tableIn special cases, the system accumulated redundant records by repeatedly failing to delete the records from the 'CMS_WebFarmTask' database table."

The one you refer to is just an improvement in case you are running a lower Azure DB tier - in that case the bulk deletion of the tasks could time out. So we introduced a setting for this so you can set lower number of tasks being processed in a bulk.

0 votesVote for this answer Mark as a Correct answer

Dmitry Bastron answered on May 6, 2020 10:04 (last edited on May 6, 2020 10:09)

Hi Kenneth,

I'm assuming you have Azure SQL, and probably Standard <100 DTU. We had similar problems with it. And as Juraj mentioned also, you have 3 options to cope with it:

Increase connectionTimeout in your connection string for CMS web app (try something like 2-5 minutes)
Switch to higher DTU tear, for us 400+ DTU worked well, or switch to Premium tear (Premium tear works much better for SQL DELETE operations)
Play with CMSWebFarmTaskDeleteBatchSize config parameter, try reducing it to 100-200

Unfortunately, there is no one ultimate solution for this issue as different sites have different reasons for tasks collected in the queue. For us it was a large amount of Staging tasks synchronizing Media Files. Because of that each task contained binary of media file and it was hard to delete these lines with default settings. So play with those 3 options and find the combination that works for you.

1 votesVote for this answer Mark as a Correct answer