A set of handy queries to inspect how much space item binaries (i.e. multimedia components, template building block assemblies) are using in the Tridion Content Manager database.

cm-binary-queries.sql

/* The queries below use data in a temp table, so at a minimum execute the queries for the temp table, and then use one or more of the queries on the BINARY table */

/*** CREATE THE TEMP TABLE ***/
/* Clear the temp table to be sure */
If(OBJECT_ID('tempdb..#CURRENT_ITEM_BINARIES') Is Not Null)
Begin
    Drop Table #CURRENT_ITEM_BINARIES
End

/* figure out which binaries are used by current (not old) versions of items */
SELECT BINARY_ID as CURRENT_BINARY_ID
    ,CONCAT('tcm:', [PUBLICATION_ID], '-', [ITEM_REFERENCE_ID], '-', [ITEM_TYPE]) as ITEM_TCM_ID
	,CONCAT('tcm:', [PUBLICATION_ID], '-', [ITEM_REFERENCE_ID], '-', [ITEM_TYPE], '-v', [VERSION]) as MAX_ITEM_VERSION,
	TITLE
INTO #CURRENT_ITEM_BINARIES
FROM [Tridion_CM].[dbo].[ITEMS] I1
WHERE I1.VERSION = (SELECT MAX(VERSION) FROM [ITEMS] I2 WHERE I2.PUBLICATION_ID = I1.PUBLICATION_ID AND I2.ITEM_REFERENCE_ID = I1.ITEM_REFERENCE_ID)
    AND BINARY_ID IS NOT NULL;

/*** GET INFORMATION ABOUT CURRENT ITEMS ***/
/* show info about binaries which are used in current versions of items */
SELECT [ID] as BINARY_ID, ([BINARIES].[CONTENT_SIZE] / 1024) AS SIZE_KB
    ,(SELECT TOP 1 (CONCAT('tcm:', [PUBLICATION_ID], '-', [ITEM_REFERENCE_ID], '-', [ITEM_TYPE]))
	  FROM ITEMS I2
	  WHERE I2.[BINARY_ID] = [BINARIES].[ID]
	  ORDER BY I2.[VERSION]) AS ITEM_TCM_ID
    ,(SELECT TOP 1 I3.[TITLE]
	  FROM ITEMS I3
	  WHERE I3.[BINARY_ID] = [BINARIES].[ID]
	  ORDER BY I3.[VERSION]) AS TITLE
FROM BINARIES
WHERE ID IN (SELECT CURRENT_BINARY_ID FROM #CURRENT_ITEM_BINARIES);

/* show total size of binaries which are used in current versions of items */
SELECT SUM(([BINARIES].[CONTENT_SIZE] / 1024)) AS TOTAL_SIZE_KB
FROM BINARIES
WHERE ID IN (SELECT CURRENT_BINARY_ID FROM #CURRENT_ITEM_BINARIES);

/*** GET INFORMATION ABOUT OLD ITEMS ***/
/* show info about binaries which are only used in old versions of items */
SELECT [ID] as BINARY_ID, ([BINARIES].[CONTENT_SIZE] / 1024) AS SIZE_KB
    ,(SELECT TOP 1 (CONCAT('tcm:', [PUBLICATION_ID], '-', [ITEM_REFERENCE_ID], '-', [ITEM_TYPE]))
	  FROM ITEMS I2
	  WHERE I2.[BINARY_ID] = [BINARIES].[ID]
	  ORDER BY I2.[VERSION]) AS ITEM_TCM_ID
    ,(SELECT TOP 1 I3.[TITLE]
	  FROM ITEMS I3
	  WHERE I3.[BINARY_ID] = [BINARIES].[ID]
	  ORDER BY I3.[VERSION]) AS TITLE
FROM BINARIES
WHERE ID NOT IN (SELECT CURRENT_BINARY_ID FROM #CURRENT_ITEM_BINARIES);

/* show total size of binaries which are only used in old versions of items */
SELECT SUM(([BINARIES].[CONTENT_SIZE] / 1024)) AS TOTAL_SIZE_KB
FROM BINARIES
WHERE ID NOT IN (SELECT CURRENT_BINARY_ID FROM #CURRENT_ITEM_BINARIES);

example-result.md

show info about binaries which are used in current versions of items

CURRENT_BINARY_ID	ITEM_TCM_ID	MAX_ITEM_VERSION	TITLE
1	tcm:1-36-2048	tcm:1-36-2048-v1	Tridion.SiteEdit.Templating
2	tcm:1-41-2048	tcm:1-41-2048-v1	Tridion.AudienceManagement.Templates
3	tcm:1-44-2048	tcm:1-44-2048-v1	Tridion.Ugc.Templating.DefaultTemplates
4	tcm:1-49-2048	tcm:1-49-2048-v1	External Content Library templating assembly
5	tcm:1-54-2048	tcm:1-54-2048-v1	Tridion.Extensions.ContextExpressions.Templates
6	tcm:1-55-2048	tcm:1-55-2048-v1	Tridion.SmartTarget.Templating
7	tcm:2-71-2048	tcm:2-71-2048-v1	Sdl.Web.Tridion.Templates.merged
8	tcm:2-89-2048	tcm:2-89-2048-v1	DD4T.Templates.Merged
9	tcm:2-102-16	tcm:2-102-16-v1	HTML Design
10	tcm:2-103-16	tcm:2-103-16-v1	Build Files
11	tcm:2-104-16	tcm:2-104-16-v1	Favicon
12	tcm:3-222-16	tcm:3-222-16-v1	Large Image
13	tcm:3-225-16	tcm:3-225-16-v1	Medium Image
14	tcm:3-226-16	tcm:3-226-16-v1	YouTube Video
15	tcm:3-240-16	tcm:3-240-16-v1	Another Large Image
16	tcm:4-258-16	tcm:4-258-16-v1	Company News Media Manager Video
17	tcm:4-259-16	tcm:4-259-16-v1	planes
18	tcm:4-261-16	tcm:4-261-16-v1	bulls-eye
19	tcm:4-262-16	tcm:4-262-16-v1	skyscrapers
20	tcm:4-264-16	tcm:4-264-16-v1	map
21	tcm:4-265-16	tcm:4-265-16-v1	blueprint
22	tcm:4-270-16	tcm:4-270-16-v1	calculator
23	tcm:4-272-16	tcm:4-272-16-v1	balloons
24	tcm:4-274-16	tcm:4-274-16-v1	bridge
25	tcm:4-276-16	tcm:4-276-16-v1	Company Logo 540 px
26	tcm:4-277-16	tcm:4-277-16-v1	Company Report 2014
27	tcm:4-278-16	tcm:4-278-16-v1	Company Report 2013
1062	tcm:4-279-16	tcm:4-279-16-v2	ballon-burner
29	tcm:4-282-16	tcm:4-282-16-v1	wine-cellar
30	tcm:4-287-16	tcm:4-287-16-v1	wall
31	tcm:4-288-16	tcm:4-288-16-v1	skyline
44	tcm:5-451-16	tcm:5-451-16-v1	Movie Projector
45	tcm:4-453-16	tcm:4-453-16-v2	Finding Nemo movie poster
1061	tcm:4-454-16	tcm:4-454-16-v2	Up
47	tcm:4-455-16	tcm:4-455-16-v1	Undercurrent
48	tcm:4-463-16	tcm:4-463-16-v3	New Company Logo 540 px
61	tcm:4-583-16	tcm:4-583-16-v1	New Component

show total size of binaries which are used in current versions of items

TOTAL_SIZE_KB
44467

show info about binaries which are only used in old versions of items

BINARY_ID	SIZE_KB	ITEM_TCM_ID	TITLE
28	863	tcm:4-279-16	ballon-burner
46	48	tcm:4-454-16	Up

show total size of binaries which are only used in old versions of items

TOTAL_SIZE_KB
911