5

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?

  • Why is UnversionedFields being omitted from the above? – Mark Cassidy Aug 18 '17 at 10:31
  • @MarkCassidy just checked: the Blob field on the "unversioned" file template actually is not unversioned but just shared. So unless this template has been changed there is no need to check blobs against the UnversionedFields table. – Mark Lowe Sep 12 '18 at 7:58
4

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.

| improve this answer | |
1

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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.