Sitecore Publishing Service was introduced with Sitecore 8.2. It uses different approach than good old Sitecore Publish as it uses SQL Bulk Copy to push content from Master DB to publishing targets and it’s also a separate service / role so it’s no more part of CM role. It is currently in version 7 so pretty mature.

To get more information, here are some good starting points:

Data Structure

Although Sitecore Publishing Service acts as a “microservice” or separate service / role, it still creates its own tables under your master DB.

Particularly these tables:

  • Publishing_ActivationLock
  • Publishing_Data_Params_FieldIds
  • Publishing_Data_Params_Languages
  • Publishing_JobManifest
  • Publishing_JobMetadata
  • Publishing_JobQueue
  • Publishing_ManifestOperationResult
  • Publishing_ManifestStatus
  • Publishing_PublisherOperation
  • Publishing_TargetSyncState

Scheduled tasks

The Publishing Service has many features. One of them is that it enables you to configure independent tasks in the system. It contains four task definitions by default:

  1. PublishTask – the task that handles requests to publish items from sources to targets.
  2. PublishJobCleanUpTask – the task that handles the periodic clean-up of historical publishing jobs.
  3. PublishOperationCleanUpTask – the task that removes publishing operations that have been processed.
  4. PublishOperationAgeBasedCleanUpTask – the task that handles periodic clean-up of historical publishing operations.

Basically, these tasks are just clearing records from tables mentioned in Data Structure section earlier in this blog post based on their purpose.

Tuning

By default, PublishOperationCleanupTask and PublishOperationAgeBasedCleanupTask are configured to run once a day. You can find this configuration under \sitecore\Sitecore.Framework.Plugin.Publishing\Config\sc.publishing.web.command.services.xml

In our case, the Publishing Service web app was scheduled to be restarted every day at 7 a.m. + this setup of running these tasks every day -> result -> they were not executed at all as the interval was not met 🙂

We have fine tune the setting to be 12 hours by changing the values to 12:00:00.

This led us to timeout issues cause there were more than 5 million records in PublishingOperations table already accumulated.

Time out issues

We have seen these exceptions in our logs:

2024-01-22 11:39:26.347 +00:00 [Error] Error processing scheduled task : "PublishOperationAgeBasedCleanupTask-4cf72df522dc4ff39e6ea98548754fd6"

System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

 ---> System.ComponentModel.Win32Exception (258): The wait operation timed out.

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

   at System.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)

   at System.Data.SqlClient.SqlCommand.EndExecuteNonQuery(IAsyncResult asyncResult)

   at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)

--- End of stack trace from previous location where exception was thrown ---

   at Dapper.SqlMapper.ExecuteImplAsync(IDbConnection cnn, CommandDefinition command, Object param)

   at Sitecore.Framework.Publishing.Service.Sql.PublisherOperations.PublisherOperationProvider.<>c__DisplayClass10_0.<<DeleteByDate>b__0>d.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at Sitecore.Framework.TransientFaultHandling.Sql.SqlRetryHelper.<>c__DisplayClass8_0`1.<<ExecuteAsync>b__0>d.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at Sitecore.Framework.TransientFaultHandling.Sql.SqlRetryHelper.ExecuteAsync[T](DbConnection connection, Func`1 sqlWork, Func`3 commandRetryPolicy, CancellationToken cancellationToken)

   at Sitecore.Framework.Publishing.Data.AdoNet.DatabaseConnection`1.ExecuteAsync[T](Func`2 dbWork)

   at Sitecore.Framework.Publishing.Service.Sql.PublisherOperations.PublisherOperationProvider.DeleteByDate(IDatabaseConnection connection, DateTime beforeDate)

   at Sitecore.Framework.Publishing.PublisherOperations.PublisherOperationRepository.RemoveOperationsBeforeDate(DateTime beforeDate)

   at Sitecore.Framework.Publishing.Tasks.PublishOperationAgeBasedCleanupTask.OnExecute(IScheduledTaskExecutionContext context)

   at Sitecore.Framework.Scheduling.ScheduledTask.Run(IEnumerable`1 taskStates)

ClientConnectionId:5ccef9a5-2840-4a4a-b725-ab1c82c6ca4f

Error Number:-2,State:0,Class:11

ClientConnectionId before routing:7d044c6b-acef-4562-acb3-eee4e6fe924a

Routing Destination:eb1f5caef5bf.tr41711.westeurope1-a.worker.database.windows.net,11043

To fix it we have contacted Sitecore Support but their solution was not helping us.

Basically we had two options:

  1. Increase timeout
  2. Clean up tables so the schedule jobs are not working with big data set hence it should run quickly…

Of course, I have chosen the second option as I hate solving only downside of the problem, not the core problem…

I have therefore created our own set of 6 SQL Scripts to overcome this problem. Here you go!

1)

DELETE FROM Publishing_PublisherOperation
WHERE [Timestamp] < DATEADD(day, -1, GETUTCDATE())

2)

DELETE
FROM Publishing_ManifestOperationResult
WHERE ManifestId IN (
SELECT manifest.[ManifestId]
FROM Publishing_JobQueue as job
RIGHT JOIN Publishing_JobManifest manifest
ON manifest.[JobId] = job.[JobId]
where job.[JobId] IS NULL)

3)

DELETE
FROM Publishing_ManifestStep
WHERE ManifestId IN (
SELECT manifest.[ManifestId]
FROM Publishing_JobQueue as job
RIGHT JOIN Publishing_JobManifest manifest
ON manifest.[JobId] = job.[JobId]
where job.[JobId] IS NULL)

4)

DELETE
FROM Publishing_ManifestStatus
WHERE ManifestId IN (
SELECT manifest.[ManifestId]
FROM Publishing_JobQueue as job
RIGHT JOIN Publishing_JobManifest manifest
ON manifest.[JobId] = job.[JobId]
where job.[JobId] IS NULL)

5)

DELETE
FROM Publishing_JobManifest
WHERE ManifestId IN (
SELECT manifest.[ManifestId]
FROM Publishing_JobQueue as job
RIGHT JOIN Publishing_JobManifest manifest
ON manifest.[JobId] = job.[JobId]
where job.[JobId] IS NULL)

6)

DELETE
FROM Publishing_JobMetadata
WHERE JobId NOT IN (
SELECT job.[JobId]
FROM Publishing_JobQueue as job)

Hopefully this will help you understand a bit Sitecore Publishing Service Scheduled Jobs and help you resolve some time out issues! Happy Sitecoring!