Apologies for the long post. We've encountered the 'Remove Unrelated Content Assets' FAILED error, previously documented here and here. I've done some investigation and interested in getting some feedback on a possible workaround using a scheduled job to log issues or optionally move content.

The issue may have been somewhat addressed in a fairly recent update? - 12.1.0: https://world.optimizely.com/support/bug-list/bug/CMS-18001 , however upgrading to version 12 may not be an immediate option in our case.

In troubleshooting, I've found that the issue is easily recreated in Alloytec using fairly standard authoring steps (documented below). Basically, the edit interface allows an author to create a cross-reference to content that is stored in another page's content asset folder.

I've also found another issue where the 'Remove Unrelated Content Assets' job will delete content in use on the website. Again, this can be reproduced in Alloytec but is a bit of an edge case. It may be a bigger issue for some sites depending on certain property type usage, so I've submitted a separate post here.

Firstly, to create the cross-reference issue in Alloytec (v11.15, issue also replicated in a site running v11.20.9):

1. Create a new 'Standard Page', let's call it 'Page 1'.
2. While on 'Page 1', go to the Assets Pane, click on 'Media' > 'For this page', then upload an image with a name like 'logo.png'. This uploads the image to the Content Asset Folder of 'Page 1'
3. Publish 'Page 1'
4. Go to the Start page and create a new Jumbotron block. Fill in the required fields
5. Go to the 'Content' tab of the jumbotron and click on the Image property. The 'Select image' dialog appears. In the search, type 'logo'. The image named 'logo' uploaded to 'Page 1' should appear
6. Select this and publish the Jumbotron
7. Go back to the Start page and drop the new jumbotron on the Large Content Area. Publish the start page
8. Now the start page is displaying an image that is stored in the content asset folder of another page
9. Move 'Page 1' to the trash, then delete it from the trash. If the Trash appears to be blank, you may need to update the permissions
10. Now go to Admin mode > Scheduled Jobs > Remove Unrelated Content Assets > Start manually.
11. The job will fail, because the new Jumbotron displayed on the start page has a reference to the logo image the job is trying to delete

This blog post helped to start identifying content stopping the job: https://world.optimizely.com/blogs/Antti-Alasvuo/Dates/2021/3/remove-unrelated-content-resources-scheduled-job-failing/ . However, it quickly became apparent we have lots of these cross-references stopping the job. I investigated the 'Force Delete' option, but in our case, some of the content was still in use. This is the same as the situation created in the Alloytec site above. Doing a force delete on the Alloytec site db using the stored proc will result in the 'logo' image disappearing from the Start page.

So I tried another option which seems to work okay, but I'm keen to get some feedback in case there are other issues to consider. I haven't run this in PROD yet!

Ultimately I had a closer look at the tblContent table. It appears that only content asset folders have a value for the 'ContentOwnerID' field.

I used this SQL to return all Content Asset Folders that have been orphaned (their owner page deleted):

SELECT tbc.pkid
FROM [EPi].[dbo].[tblContent] tbc
left join[Epi].[dbo].[tblContent] tbc2
on tbc.ContentOwnerID = tbc2.ContentGUID
where tbc.ContentOwnerID is not null
and tbc2.pkID is null

Using this list of ids, I could then retrieve all the references to the content contained in these folders. In the end I created the following scheduled job, that can either just print out a list of troublesome content for manual intervention, or optionally move that content programmatically to a Global Asset folder that has been created specifically in Edit mode. Once the cross-referenced content was moved, the Remove Unrelated Content Assets scheduled job started successfully.

    [ScheduledPlugIn(DisplayName = "Move Cross Referenced Content")]
    public class MoveCrossReferencedContentScheduledJob : ScheduledJobBase
    {
        private bool _stopSignaled;

        private const int CrossReferencedContentFolder = add id here; // Create new folder under Global assets ('For All Sites'), add its ID here

        public MoveCrossReferencedContentScheduledJob()
        {
            IsStoppable = true;
        }

        /// <summary>
        /// Called when a user clicks on Stop for a manually started job, or when ASP.NET shuts down.
        /// </summary>
        public override void Stop()
        {
            _stopSignaled = true;
        }

        /// <summary>
        /// Called when a scheduled job executes
        /// </summary>
        /// <returns>A status message to be stored in the database log and visible from admin mode</returns>
        public override string Execute()
        {
            //Call OnStatusChanged to periodically notify progress of job for manually started jobs
            OnStatusChanged(String.Format("Starting execution of {0}", this.GetType()));

           
            IContentRepository contentRepository = ServiceLocator.Current.GetInstance<IContentRepository>();


            // List for holding content IDs that are orphaned and may have cross-referenced descendants
            List<int> contentIds = new List<int>();

            // Select all content asset folders that have no owner
            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["EPiServerDB"].ConnectionString))
            {
                connection.Open();
                var cmd = new SqlCommand
                {
                    Connection = connection,
                    CommandText = @"SELECT tbc.pkid
                                    FROM [Epi].[dbo].[tblContent] tbc
                                    left join[Epi].[dbo].[tblContent] tbc2
                                    on tbc.ContentOwnerID = tbc2.ContentGUID
                                    where tbc.ContentOwnerID is not null
                                    and tbc2.pkID is null",
                    CommandType = CommandType.Text
                };

                var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    contentIds.Add((int)reader[0]);
                }
                cmd.Dispose();
                connection.Close();
            }

            OnStatusChanged(string.Format("Found {0} orphaned content asset folders", contentIds.Count()));

            List<ReferenceInformation> crossReferencedContent = new List<ReferenceInformation>();

            // Get a list of all references to the contents of the assets folders
            foreach (var contentAssetFolder in contentIds)
            {
                // For each content asset folder that has no owner, get all references to its descendant content
                var contentReferences = contentRepository.GetReferencesToContent(new ContentReference(contentAssetFolder), true).ToList();
                crossReferencedContent.AddRange(contentReferences);
            }

            // Option 1: log or print out a list of problematic content references for local troubleshooting
            System.IO.File.WriteAllLines("C:\\temp\\CrossReferencedContent.txt", crossReferencedContent.Select(x => string.Format("Content with ID {0} references {1}", x.OwnerID.ID.ToString(), x.ReferencedID.ID.ToString())));

            // There can be more than one cross-reference to specific content, so just get the unique content ids that are referenced
            var uniqueContentItemList = crossReferencedContent.Select(x => x.ReferencedID.ID).Distinct();

            // Option 2: Uncomment to use. Move all the cross-referenced content out of Content Asset Folders that are flagged for deletion, and into a Global Asset Folder
            //foreach (var item in uniqueContentItemList)
            //{
            //    // Move the content
            //    contentRepository.Move(new ContentReference(item), new ContentReference(CrossReferencedContentFolder), EPiServer.Security.AccessLevel.NoAccess, EPiServer.Security.AccessLevel.NoAccess);
            //}

            //For long running jobs periodically check if stop is signaled and if so stop execution
            if (_stopSignaled)
            {
                return "Stop of job was called";
            }

            return String.Format("Moved {0} content items from orphaned Content Asset Folders", uniqueContentItemList.Count());
        }

      
    }