Remove Unrelated Content Resources scheduled job failing

This is quick post about a situation where the Episerver scheduled job "Remove Unrelated Content Resources" started to fail because of a database constraint. I would say that this is a really rare case (seen it happen only in two projects) but sharing the information just in case you face the same thing in your project so you are able to solve it quicker ;-)

So when this error occurs the scheduled job records error message "The DELETE statement conflicted with the REFERENCE constraint "FK_tblContentProperty_tblContent2". The conflict occurred in database "YOUR-DB-NAME-HERE", table "dbo.tblContentProperty", column 'ContentLink'.". No that helpful message :-/ something wrong in the database BUT you would be interested to know what content caused the issue, so you might head to your applications error logs and hope to see the offending content id there, but sadly it is not logged (WINK, Episerver maybe change the scheduled job and/or the ContentDB to log the offending content id?).

Remove Unrelated Content Resources scheduled job

So the job is defined in EPiServer.dll and the class is CleanUnusedAssetsFoldersJob in EPiServer.Util namespace. Having a look at the code in ILSpy I could track the code path and see if there are any places where I would get some log messages to help find the offending content id. I was lucky, the code eventually endsup to the 'ContentSaveDB.Delete(ContentReference contentLink, bool forceDelete)' method (in EPiServer.dll, namespace EPiServer.DataAccess.Internal). In that method there is a debug log message writen before the content is deleted using the a stored procedure 'editDeletePage' which takes the content id and boolean force delete parameters.

Log messages from ContentSaveDB.Delete

So next we need to modify our logging to log debug messages from the ContentSaveDB class. Here is a sample using the Episerver default log4net logging framework, if you have replaced log4net with something else you anyways get the idea what you want to log.

In your Episerver log configuration add new appender or ensure that the appender you use allows the debug messages to be writen, here is a demo appender I used to log the messages to a separate file (notice there is no threshold or ranges for log levels configured):

<appender name="debugLogAppender" type="log4net.Appender.RollingFileAppender" >
	<file value="App_Data\logs\DebugMessages.log" />
	<encoding value="utf-8" />
	<staticLogFileName value="true"/>
	<datePattern value=".yyyyMMdd.'log'" />
	<rollingStyle value="Date" />
	<appendToFile value="true" />
	<layout type="log4net.Layout.PatternLayout">
		<conversionPattern value="%date [%thread] %level %logger: %message%n" />
	</layout>
</appender>

And then we need a logger definition that uses the above appender to actually log the messages (using additivity="false" here so that the messages matching this logger areonly logged here and not passed to other loggers):

<logger name="EPiServer.DataAccess.Internal.ContentSaveDB" additivity="false">
	<level value="All" />
	<appender-ref ref="debugLogAppender" />
</logger>

So next step is to run the the failing job again with the above logging configured and now we get to the log debug messages that tell us the offending content id, like this: "DEBUG EPiServer.DataAccess.Internal.ContentSaveDB: Deleting content 115". So basically the last debug message about "deleting content" before the error in database happens is your offending content id and you can use that content id then to see what content the offending content is (hint: take that id and browse to it in Episerver edit mode, so when in edit mode for example on the site start page modify your browser url and replace the content id in "epi.cms.contentdata:///5" with the offending content id value like this "epi.cms.contentdata:///115").

So this way you can find the offending content and try to troubleshoot and understand what has caused the situation and should you actually care or just get rid of the content the "force" way.

Deleting the offending content

The first time we faced this issue we contacted Episerver support to get knowledge has this happened in other projects and is there any "supported" way to fix it. This is not common and from Episerver support we just got information that we should edit the 'editDeletePage' stored procedure in the database and set it temporary to use the force delete always (see the @ForceDelete argument in the stored procedure and set it to be 1 always inside the stored procedure) and then run the scheduled job and revert the change to the stored procedure. Naturally there is the disclaimer that you need to test it and take backups if that doesn't work for some reason or causes new issues so you can revert the database to the otherwise working state.

But now that you know what is the offending content id you could skip the stored procedure modifications and instead execute it with the offending content id and call it with @ForceDelete value 1 from SQL Server Management Studio or from command line like this:

USE [YOUR-DATABASE-NAME-HERE]
GO
DECLARE	@return_value Int
EXEC	@return_value = [dbo].[editDeletePage]
		@PageID = THE-CONTENT-ID-HERE,
		@ForceDelete = 1
SELECT	@return_value as 'Return Value'
GO

And after executing that you would run the scheduled job again which could still faill because you might have more than one offending content ids but then you would just execute the stored procedure again with the new offending content id.

I hope you never face this same issue but if you do, then you can use the above to fix the situation.