How to remove media item from both blobs table and items table at the same time from sitecore master db?

Question

I have added some images in sitecore media library. Those images are getting added in both dbo.Blobs and dbo.Items tables. I wanted to delete the image data from both the tables.But I don't know how to delete the entries in both the tables at the same time.

I want to know, by using which field these tables are related to each other.

Answer 1

If you have orphaned blob items in your database, you should use Clean up databases tool from Sitecore Control Panel:

It:

• Removes all invalid language items, i.e., items for which the language does not exist
• Removes field data for non-existing items, including orphan fields
• Removes orphaned items, i.e., items that do not have a parent item
• Removes unused Blob fields of media items
• Rebuilds the descendants tree
• And finally, clears all Sitecore caches

Source: Improving Sitecore performance – by reducing volume of content

Answer 2

The Clean Up Databases command doesn't always work when there are a lot of items that need cleaning up. Try this SQL query for cleaning up the Blobs table to remove orphaned rows:

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 (10000) from [Blobs] 
where [BlobId] not in (select * from @UsableBlobs)