Tech with Jagmeet

In the Sitecore azure blob migration, one of the consideration for using azure blob storage module is to blobs cleanup.

Blobs cleanup is to remove any orphaned blobs so that we do not consume time and space migrating blobs that are not in use.

Before starting, I ran blobs cleanup(via link ). It ran for couple of hours with no change.

Looking at the logs, it appears that it broke because of SQL timeout error(refer below)

ManagedPoolThread #7 00:04:56 ERROR Failed to remove unused blobs
Exception: System.Data.SqlClient.SqlException
Message: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Create a patch file to update following:

• Decrease the value in \app_config\include\sitecore.config
  <setting name=”Sitecore.CleanupBlobsBatchSize” value=”1000″ />
• Increase the timeout in \app_config\include\sitecore.config
  <setting name=”DefaultSQLTimeout” value=”00:05:00″ />

and run the cleanup again. It took couple of hours and was successful.

Alternative approach:

Every after changing the Sitecore.CleanupBlobsBatchSize and DefaultSQLTimeout value, cleanup was giving issues and executing for long time in different environment. So I created following T-SQL scripts to delete the unused/orphaned blobs.

Before running this script, make sure that all blob values are in same format.

• Remove the curly braces if present
• Convert all into small case

DECLARE @r INT;
DECLARE @batchsize INT;

SET @r = 1;
SET @batchsize=1000;


WHILE @r > 0
  BEGIN
      BEGIN TRANSACTION;

      delete top (@batchsize) from Blobs
WHERE  CAST(BlobId AS varchar(64))  not IN
    (
        SELECT CAST(Value AS varchar(64))
        FROM   SharedFields WITH (NOLOCK)
        WHERE  FieldId = '40E50ED9-BA07-4702-992E-A912738D32DC' 
        UNION
        SELECT CAST(Value AS varchar(64))
        FROM   VersionedFields WITH (NOLOCK)
        WHERE  FieldId = 'DBBE7D99-1388-4357-BB34-AD71EDF18ED3'
        UNION
        SELECT CAST(Value AS varchar(64))
        FROM   ArchivedFields WITH (NOLOCK)
        WHERE  FieldId IN ('40E50ED9-BA07-4702-992E-A912738D32DC', 'DBBE7D99-1388-4357-BB34-AD71EDF18ED3')
    );

      SET @r = @@ROWCOUNT;

      COMMIT TRANSACTION;
  END

This is comparatively faster, relying, accurate and you will get the count easily.

Hope this helps!