Sitecore.UpdateApp removing blob error

Question

I am following the upgrade guide for Sitecore Experience Platform to migrate from Sitecore 8.2 to 10.1.

One of the steps is to run the Sitecore.UpdateApp console tool. I have run the console app in my server and getting the following error.

ERROR Failed to remove unused blobs
Exception: System.Exception
Message: Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. 

While getting the following error in the local environment

ERROR Failed to remove unused blobs
Exception: System.Exception
Message: The Database ID 7, Page (1:317184), slot 0 for LOB data type node does not exist. This is usually caused by transactions that can read uncommitted data on a data page. Run DBCC CHECKTABLE.
Source: Sitecore.Kernel

Error in Server and Local environment is same,i.e ERROR Failed to remove unused blobs but different Messages.

Has anyone experienced this issue?

Answer 1

We had a similar issue with cleaning up blobs, the in built Sitecore mechanisms were not working (i.e. control panel => cleanup database). Upon inspecting the logs it showed always 0 processed. Turns out the SQL process was timing out.

Initial options are to try increasing the DefaultSQLTimeout setting, and/or decreasing the CleanupBlobsBatchSize setting.

However, due to the volume and size of the blobs we had to cleanup, the above didnt work. So we needed to do this directly against the database.

Try running the following script.

SELECT_Orphaned_Blobs

WITH ExistingBlobs (BlobId) AS (  
    SELECT Blobs.BlobId FROM Blobs
        INNER JOIN SharedFields
            ON '{' + CONVERT(NVARCHAR(MAX), Blobs.BlobId) + '}' = SharedFields.Value

    UNION
    SELECT Blobs.BlobId FROM Blobs
        INNER JOIN VersionedFields
            ON '{' + CONVERT(NVARCHAR(MAX), Blobs.BlobId) + '}' = VersionedFields.Value

    UNION
    SELECT Blobs.BlobId FROM Blobs
        INNER JOIN UnversionedFields
            ON '{' + CONVERT(NVARCHAR(MAX), Blobs.BlobId) + '}' = UnversionedFields.Value

    UNION
    SELECT Blobs.BlobId FROM Blobs
        INNER JOIN ArchivedFields
            ON '{' + CONVERT(NVARCHAR(MAX), Blobs.BlobId) + '}' = ArchivedFields.Value
)

SELECT COUNT(1) FROM Blobs
    LEFT JOIN ExistingBlobs ON Blobs.BlobId = ExistingBlobs.BlobId
WHERE ExistingBlobs.BlobId IS NULL

If the above returns a high volume of results and also takes a long time to execute, then you most likely be hitting the same timeout issues we had.

The best solution is to try to remove the blobs using the sitecore "cleanup databases" task. However, if when you run the task you get log like this:

Job ended: CleanUpDatabases (units processed: )

Then you could try removing the blobs manually, before then running the cleanup tool (which will then not timeout).

Obviously take a backup of the master DB and test this from all angles first! But the scripts we used were as follows:

DELETE_Orphaned_Blobs_In_Batches

IF OBJECT_ID('tempdb..#ExistingBlobs') IS NOT NULL
BEGIN
    DROP TABLE #ExistingBlobs
END

CREATE TABLE #ExistingBlobs (BlobId uniqueidentifier NOT NULL)

INSERT INTO #ExistingBlobs (BlobId)
    SELECT Blobs.BlobId FROM Blobs
        INNER JOIN SharedFields
            ON '{' + CONVERT(NVARCHAR(MAX), Blobs.BlobId) + '}' = SharedFields.Value

    UNION
    SELECT Blobs.BlobId FROM Blobs
        INNER JOIN VersionedFields
            ON '{' + CONVERT(NVARCHAR(MAX), Blobs.BlobId) + '}' = VersionedFields.Value

    UNION
    SELECT Blobs.BlobId FROM Blobs
        INNER JOIN UnversionedFields
            ON '{' + CONVERT(NVARCHAR(MAX), Blobs.BlobId) + '}' = UnversionedFields.Value

    UNION
    SELECT Blobs.BlobId FROM Blobs
        INNER JOIN ArchivedFields
            ON '{' + CONVERT(NVARCHAR(MAX), Blobs.BlobId) + '}' = ArchivedFields.Value

DECLARE @t INT;
DECLARE @batchsize INT;

SET @batchsize = 50;
SET @t = @batchsize;

WHILE @t > 0
BEGIN
BEGIN TRANSACTION;

    DELETE TOP (@batchsize) Blobs FROM Blobs
        LEFT JOIN #ExistingBlobs ON Blobs.BlobId = #ExistingBlobs.BlobId
    WHERE #ExistingBlobs.BlobId IS NULL

    SET @t = @@ROWCOUNT;

COMMIT TRANSACTION;
END

IF OBJECT_ID('tempdb..#ExistingBlobs') IS NOT NULL
BEGIN
    DROP TABLE #ExistingBlobs
END

Credit to sitecore support for the above SQL scripts.

They were provided specifically to address the problem we were facing, but should be useful here also.