Skip to content

Instantly share code, notes, and snippets.

@janhebnes
Created October 24, 2012 15:06
Show Gist options
  • Save janhebnes/3946630 to your computer and use it in GitHub Desktop.
Save janhebnes/3946630 to your computer and use it in GitHub Desktop.
Sitecore CMS - SQL for Removing Non Image Blobs from a Sitecore Database Blob table
-- Large blob data in test/uat environments is just wastefull use of disk space
SP_SPACEUSED [Blobs]
-- FINDING ItemIds FOR RELEVANT FIELDS
-- File > Information > Extension Field
select * from Items where ID like 'C06867FE-9A43-4C7D-B739-48780492D06F'
-- File > Media > Blob Field
select * from Items where ID like '40E50ED9-BA07-4702-992E-A912738D32DC'
-- Examining the data location of the fields
select * from UnversionedFields where Value like 'pdf' and FieldId like 'C06867FE-9A43-4C7D-B739-48780492D06F'
select * from SharedFields where Value like 'pdf' and FieldId like 'C06867FE-9A43-4C7D-B739-48780492D06F'
select * from VersionedFields where Value like 'pdf' and FieldId like 'C06867FE-9A43-4C7D-B739-48780492D06F'
select * from UnversionedFields where FieldId like '40E50ED9-BA07-4702-992E-A912738D32DC'
select * from SharedFields where FieldId like '40E50ED9-BA07-4702-992E-A912738D32DC'
select * from VersionedFields where FieldId like '40E50ED9-BA07-4702-992E-A912738D32DC'
-- viewing how many datatypes are stored in the database
select distinct Value from SharedFields where FieldId like 'C06867FE-9A43-4C7D-B739-48780492D06F'
select count(Value) from SharedFields where Value in ('jpeg','jpg','png','gif') and FieldId like 'C06867FE-9A43-4C7D-B739-48780492D06F'
-- evaluating if removing should be done by including or excluding specific media extension
select * from SharedFields where Value in ('pdf','zip','xls','psd','pptx','wmv','ppt','pps','ppsx') and FieldId like 'C06867FE-9A43-4C7D-B739-48780492D06F'
select ItemId from SharedFields where Value not in ('jpeg','jpg','png','gif') and FieldId like 'C06867FE-9A43-4C7D-B739-48780492D06F'
-- Find all BlobId's related to files that are not of of extension jpeg, jpg, png or gif
select Value from SharedFields where FieldId like '40E50ED9-BA07-4702-992E-A912738D32DC'
and ItemId in (select ItemId from SharedFields where Value not in ('jpeg','jpg','png','gif') and FieldId like 'C06867FE-9A43-4C7D-B739-48780492D06F')
-- Select the bloatted Blobs (Notice that we cast to varchar and remove excessive { } from value source prior to comparing)
select COUNT(*) from [blobs]
where Cast(BlobId AS varchar(200)) in (
select Replace(Replace(Cast(Value AS varchar(200)),'{',''),'}','') from SharedFields
where FieldId like '40E50ED9-BA07-4702-992E-A912738D32DC'
and ItemId in (
select ItemId from SharedFields where Value not in ('jpeg','jpg','png','gif') and FieldId like 'C06867FE-9A43-4C7D-B739-48780492D06F'
)
)
-- READY TO REMOVE THE BLOATED BLOBS THAT ARE NOT image FILES / Two different approaches
/*
-- DELETE ONLY FOUND FILE BLOBS
DELETE FROM [blobs]
where Cast(BlobId AS varchar(200)) in (
select Replace(Replace(Cast(Value AS varchar(200)),'{',''),'}','') from SharedFields
where FieldId like '40E50ED9-BA07-4702-992E-A912738D32DC'
and ItemId in (
select ItemId from SharedFields where Value not in ('jpeg','jpg','png','gif') and FieldId like 'C06867FE-9A43-4C7D-B739-48780492D06F'
)
)
-- DELETE EVERYTHING EXCEPT IMAGE FILES (uses left outer join for better speed)
DELETE FROM [blobs] where Id IN (
select Id from [blobs] AA
LEFT OUTER JOIN (
select Replace(Replace(Cast(Value AS varchar(200)),'{',''),'}','') as ValidBlobId from SharedFields
where FieldId like '40E50ED9-BA07-4702-992E-A912738D32DC'
and ItemId in (
select ItemId from SharedFields where Value in ('jpeg','jpg','png','gif') and FieldId like 'C06867FE-9A43-4C7D-B739-48780492D06F'
)) BB ON Cast(AA.BlobId AS varchar(200)) = BB.ValidBlobId
WHERE BB.ValidBlobId IS NULL
)
*/
-- Files left in blobs
Select COUNT(*) from blobs
-- Large blob data is now gone :) disk space has been recovered for a better world
SP_SPACEUSED [Blobs]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment