Last updated on January 10th, 2023 at 09:04 am
The other day, a good friend of mine reached out and asked if I had an article on Calculating ConfigMgr Custom Inventory Database Size. I told him that I didn’t have an article on the subject. He told he that it would be a good topic, so here is this article.
As a Microsoft Configuration Manager (MEMCM / MECM / SCCM / ConfigMgr / MCM) administrator wears many hats. (Don’t you love how Microsoft keep changing the name for ConfigMgr?) Inventory more data with SCCM, is one of these tasks. But we all have to live with Change Advisory Board (CAB) or SQL administrator that will want to know more details.
Answering question about how much CPU a custom script will take are easy. Just run the script and determine how long it takes. Most inventory scripts will be done within seconds. If want to inventory Registry keys then using results of by article How To Use Regkeytomof will be added to the Hardware inventory. This will ultimately mean no one will notice this either.
But How?
But how do you answer the question ConfigMgr Custom Inventory Database Size?
The simplest way it to load the custom inventory within your SCCM lab. Your lab will of course only have a few computers. You can then do some “rough” math to determine how much you will increase the database size.
In my video shown within How To Use Regkeytomof, I inventoried registry key for CVE-2021-26414. For more details about the CVE, please see the Microsoft KB5004442 on the subject. In a nutshell I added one key to SCCM hardware inventory (RequireIntegrityActivationAuthenticationLevel) but how much will that increase my “production” db with 10000 computer? I will use my lab to extrapolate how much data is collected per computer then I can do “rough” math to show how much that one registry key increases my db for 10000 computers.
Let’s find out.
Calculating ConfigMgr Custom Inventory Database Size
For this I have already added the MOF edit to my lab and I have forced all computers to preform a Hardware inventory. Thereby returning the result to the database. I then ran a query to see ConfigMgr Custom Inventory Table Sizes.
Accordingly, in the screenshot about we can see that there are 3 tables and 3 views. The tables use 216 KB within the database. But, there is only 48KB used out of the 216 KB. Within the lab there are 14 active computers. This mean 48kb /14 = 3.4 KB (rounded) per computer. Therefore for 10000 computers, I would be looked approximately 33.5 MB. All things considered, this is where I would round this number up to 40MB and call it a day. This should be good for Both SQL Administrator or any CAB.
ConfigMgr Custom Inventory Database Size SQL Query
SELECT
SUM(a.total_pages) * 8 AS 'KB',
(SUM(a.total_pages) * 8) / 1024.00 AS 'MB',
SUM(a.used_pages) * 8 AS 'Used KB',
(SUM(a.used_pages) * 8) / 1024.00 AS 'Used MB'
FROM
sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.name like 'CVE%'
ConfigMgr Custom Inventory Database Size Video
SUM(a.total_pages) * 8 AS ‘KB’,
(SUM(a.total_pages) * 8) / 1024.00 AS ‘MB’,
SUM(a.used_pages) * 8 AS ‘Used KB’,
(SUM(a.used_pages) * 8) / 1024.00 AS ‘Used MB’
FROM
sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.name like ‘CVE%’
ConfigMgr Custom Inventory Database Size Video
What else would you like to know about ConfigMgr Custom Inventory? Since I will be putting out a lot more videos on YouTube. Additionally, don’t forget to subscribe and click the like button. I will help bring the videos to admins like you.
Finally, don’t forget that you can subscribe to my RRS feed to stay on top of the latest trips and tricks. Additionally, If you have any questions, please feel free to touch base @Garthmj.