What size is your CM Inventory History?

April 23, 2026 by Jay Connor

What size is your CM Inventory History?

SCCM functions slowing down such as collection update, run scripts? Probably some SQL/Options that needs tuning.
I dont think Config Manager was designed to handle some of the newer inventory data at least at the frequency we get it. Having large tables of unneeded history will slow you down. If you are getting history like daily it will take up a lot of space

Get the History size

SELECT 
    t.name,
    SUM(p.rows) AS row_count,
    SUM(a.total_pages) * 8 / 1024 AS size_mb
FROM sys.tables t
JOIN sys.partitions p ON p.object_id = t.object_id AND p.index_id IN (0,1)
JOIN sys.allocation_units a ON a.container_id = p.partition_id
WHERE t.name LIKE '%HIST%'
GROUP BY t.name
ORDER BY row_count DESC

SELECT

t.name,

SUM(p.rows) AS row_count,

SUM(a.total_pages) * 8 / 1024 AS size_mb

FROM sys.tables t

JOIN sys.partitions p ON p.object_id = t.object_id AND p.index_id IN (0,1)

JOIN sys.allocation_units a ON a.container_id = p.partition_id

WHERE t.name LIKE '%HIST%'

GROUP BY t.name

ORDER BY row_count DESC

Clear older than 60 days example script

SET NOCOUNT ON

DECLARE @tables TABLE (name SYSNAME, priority INT)
INSERT @tables VALUES
    ('OPTIONAL_FEATURE_HIST',              1),
    ('SYSTEM_CONSOLE_USAGE_HIST',          1),
    ('SYSTEM_CONSOLE_USER_HIST',           1),
    ('UPGRADE_EXPERIENCE_INDICATORS_HIST', 1),
    ('BROWSER_USAGE_HIST',                 1),
    ('Services_HIST',                      1),
    ('WINDOWS8_APPLICATION_USER_INFO_HIST',1),
    ('WINDOWS8_APPLICATION_HIST',          1),
    ('PNP_SIGNED_DRIVER_CUSTOM_HIST',      2),
    ('INSTALLED_SOFTWARE_HIST',            2),
    ('Network_HIST',                       2),
    ('Netcard_HIST',                       2),
    ('Logical_Disk_HIST',                  2),
    ('ClientEvents_HIST',                  2),
    ('Add_Remove_Programs_HIST',           2),
    ('Add_Remove_Programs_64_HIST',        2),
    ('Operating_System_HIST',              2),
    ('QUICK_FIX_ENGINEERING_HIST',         2),
    ('Office365ProPlusConfigurations_HIST',2),
    ('Video_Controller_HIST',              2),
    ('Computer_System_HIST',               2),
    ('Desktop_Monitor_HIST',               2),
    ('OFFICE_ADDIN_HIST',                  2),
    ('ENCRYPTABLE_VOLUME_HIST',            2),
    ('Sound_Devices_HIST',                 2),
    ('Disk_HIST',                          2),
    ('MDM_DEVDETAIL_EXT01_HIST',           2)

DECLARE @tbl SYSNAME, @sql NVARCHAR(MAX), @deleted INT, @total BIGINT, @start DATETIME, @msg NVARCHAR(500)

DECLARE c CURSOR LOCAL FAST_FORWARD FOR 
    SELECT name FROM @tables ORDER BY priority, name

OPEN c
FETCH NEXT FROM c INTO @tbl

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @deleted = 1
    SET @total = 0
    SET @start = GETDATE()
    
    SET @msg = CONCAT('--- Starting ', @tbl, ' at ', CONVERT(VARCHAR, GETDATE(), 108), ' ---')
    RAISERROR(@msg, 0, 1) WITH NOWAIT
    
    WHILE @deleted > 0
    BEGIN
        SET @sql = 'DELETE TOP (5000) FROM ' + QUOTENAME(@tbl) 
                 + ' WHERE TimeKey < DATEADD(day, -60, GETDATE())'
        EXEC sp_executesql @sql
        SET @deleted = @@ROWCOUNT
        SET @total = @total + @deleted
        
        WAITFOR DELAY '00:00:01'
    END
    
    SET @msg = CONCAT('Done ', @tbl, ': ', @total, ' rows in ', 
                     DATEDIFF(second, @start, GETDATE()), ' seconds')
    RAISERROR(@msg, 0, 1) WITH NOWAIT
    
    FETCH NEXT FROM c INTO @tbl
END

CLOSE c
DEALLOCATE c

SET NOCOUNT ON

DECLARE @tables TABLE (name SYSNAME, priority INT)

INSERT @tables VALUES

('OPTIONAL_FEATURE_HIST', 1),

('SYSTEM_CONSOLE_USAGE_HIST', 1),

('SYSTEM_CONSOLE_USER_HIST', 1),

('UPGRADE_EXPERIENCE_INDICATORS_HIST', 1),

('BROWSER_USAGE_HIST', 1),

('Services_HIST', 1),

('WINDOWS8_APPLICATION_USER_INFO_HIST',1),

('WINDOWS8_APPLICATION_HIST', 1),

('PNP_SIGNED_DRIVER_CUSTOM_HIST', 2),

('INSTALLED_SOFTWARE_HIST', 2),

('Network_HIST', 2),

('Netcard_HIST', 2),

('Logical_Disk_HIST', 2),

('ClientEvents_HIST', 2),

('Add_Remove_Programs_HIST', 2),

('Add_Remove_Programs_64_HIST', 2),

('Operating_System_HIST', 2),

('QUICK_FIX_ENGINEERING_HIST', 2),

('Office365ProPlusConfigurations_HIST',2),

('Video_Controller_HIST', 2),

('Computer_System_HIST', 2),

('Desktop_Monitor_HIST', 2),

('OFFICE_ADDIN_HIST', 2),

('ENCRYPTABLE_VOLUME_HIST', 2),

('Sound_Devices_HIST', 2),

('Disk_HIST', 2),

('MDM_DEVDETAIL_EXT01_HIST', 2)

DECLARE @tbl SYSNAME, @sql NVARCHAR(MAX), @deleted INT, @total BIGINT, @start DATETIME, @msg NVARCHAR(500)

DECLARE c CURSOR LOCAL FAST_FORWARD FOR

SELECT name FROM @tables ORDER BY priority, name

OPEN c

FETCH NEXT FROM c INTO @tbl

WHILE @@FETCH_STATUS = 0

BEGIN

SET @deleted = 1

SET @total = 0

SET @start = GETDATE()

SET @msg = CONCAT('--- Starting ', @tbl, ' at ', CONVERT(VARCHAR, GETDATE(), 108), ' ---')

RAISERROR(@msg, 0, 1) WITH NOWAIT

WHILE @deleted > 0

BEGIN

SET @sql = 'DELETE TOP (5000) FROM ' + QUOTENAME(@tbl)

+ ' WHERE TimeKey < DATEADD(day, -60, GETDATE())'

EXEC sp_executesql @sql

SET @deleted = @@ROWCOUNT

SET @total = @total + @deleted

WAITFOR DELAY '00:00:01'

END

SET @msg = CONCAT('Done ', @tbl, ': ', @total, ' rows in ',

DATEDIFF(second, @start, GETDATE()), ' seconds')

RAISERROR(@msg, 0, 1) WITH NOWAIT

FETCH NEXT FROM c INTO @tbl

END

CLOSE c

DEALLOCATE c

There are no comments yet, add one below.

What size is your CM Inventory History?

Leave a Comment Cancel reply