Why CleanUp doesn't work for the Blobs Table?

Question

We have a Sitecore 8.1.1 with Sharepoint Connector 2.1. the connector imports 4000 documents from the Sharepoint (max 10 GB). The problem is that the Blob Tables last time were more than 80 Gb on master and web.

we did CleanUp using the the Sql Query to delete the Blobs, not connected with any item.

    USE [Sitecore_web]
GO
/****** Object: Table [dbo].[Blobs] Script Date: 24/07/2017 16:41:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
delete from Blobs 
where BlobId NOT IN 
(
Select Value from SharedFields with (nolock)
where FieldId = '40E50ED9-BA07-4702-992E-A912738D32DC' and Value != ''
Union
Select Value from VersionedFields with (nolock)
where FieldId = 'DBBE7D99-1388-4357-BB34-AD71EDF18ED3'
Union
Select Value from ArchivedFields with (nolock)
where FieldId in ('40E50ED9-BA07-4702-992E-A912738D32DC','DBBE7D99-1388-4357-BB34-AD71EDF18ED3')
)

GO

But in three weeks the blobs Tables are big again and there are almost 19000 extra files.

Any idea why Sitecore doesn't do CleanUp?

Answer 1

I am having this issue as well and it is caused by the fact that the connector is NOT using the modified date and creates a new blob on every sync. We were syncing once per hour and it eventually brought down the site due to the size of the database in the elastic pool. I have submitted a ticket to Sitecore for help in getting the connector to stop this behavior. I did confirm that my modified date was being mapped.

Also you shouldn't use that query for clean your Web database as it will lock your table and bring your site to a crawl. Put it in a loop similar to this:

WHILE 1 = 1

BEGIN

    Delete from Blobs 
    WHERE BlobId IN (SELECT DISTINCT TOP 100 BlobId FROM Blobs
    where BlobId NOT IN (SELECT DISTINCT BlobId from Master_blobs))
   IF @@ROWCOUNT < 100 BREAK;
END

I had just done a full publish of my items so I could count on Master blob table. You can use your where...

Also, to answer your question. Sitecore appears to do clean up on start up, but you wouldn't want that query to run on that many orphaned blobs. You'd be better off creating your own task and running a query like my example on a regular interval.

I will try to update this once I get to the bottom of why the connector keeps creating new blobs even though the modified dates match.

Answer 2

I was able to make the following delete a bunch of blob records while trying to create a smaller backup. I recommend you verify this in a non-production environment.

WHILE 1 = 1

BEGIN

DECLARE @UsableBlobs table(ID uniqueidentifier);

INSERT INTO @UsableBlobs    
SELECT convert(uniqueidentifier,[Value]) as EmpID from [Fields]
WHERE [Value] != '' 
AND (FieldId='{40E50ED9-BA07-4702-992E-A912738D32DC}' OR FieldId='{DBBE7D99-1388-4357-BB34-AD71EDF18ED3}') 

DELETE top (100) from [Blobs] 
WHERE [BlobId] NOT IN (SELECT * FROM @UsableBlobs)

IF @@ROWCOUNT < 100 BREAK;

END

Update

Was working on an upgrade from Sitecore 9.3 to 10.2 and found that the old Web database had grown to 37GB while the new version remained at 4GB. My guess is that this was caused by an issue with publishing. I ran this script which helped identify that the blob table contained 82% more records in it. The Cleanup blobs option as part of the Database Cleanup page removed some records but not much.

After a really long time (3+ hours) the row count returned to a normal/expected level.

I believe I originally found the solution to delete from here.