ConfigMgr Collections: Dos and Don’ts

Collections are very important in ConfigMgr, though there are some limits. We will explore some of those limits and I will provide some PowerShell commands to help you.

Collection Limits

First, there is a limit to the number of collections that you have. It’s dependent on the resources that you allocate to your SQL server. The more resources, the more collections you can have. Remember that for every collection you create, two corresponding views are created in the database. This can make your database very large and can consume a lot of resources.

Collection Updates

Second, collection updates is the most resource intensive process that ConfigMgr performs. You should really think about whether or not you need incremental updates turned on and how often your collections reevaluate their membership. You should come up with a strategy as to what collections need both of these options.

To determine which collections have incremental updates enabled, run this PowerShell command (be sure to input the primary site server name and site code where indicated):

 (get-wmiobject -computername <primary site server> -namespace root\sms\site_<site code> -class SMS_Collection | where-object -filterscript {$_.RefreshType -eq “4” -or $_.RefreshType -eq “6”}).Name


Collection updates are also performed using the tempdb on your SQL server, not in the ConfigMgr database. Collection changes are then written into the ConfigMgr database. You will know fairly quickly if you have a collection update problem because this database will get very large, very fast (I once saw one grow to 20GB in four hours). The larger this database is, the slower your environment will run.

If you are having this problem, you can establish which collection is causing the problem by looking at the reports on tempdb. To do that, open SQL Management Studio. Expand Databases, then System Databases. Right-click on tempdb, select Reports, then Standard Reports, then “Top Transactions by Age”.


That will open the report and you can see what is causing the database to fill up. Unfortunately, it does not give you a collection name, but hopefully you can determine which collection it is by the query (it displays the query that is used to build the collection).

like and not like

If you can help it, do not use like or not like when building your collection queries. These are very database intensive, and again, could fill up your tempdb. To determine if you are using any collections with “like” or “not like”, run this SQL query:

 select SiteID, CollectionName

from Collections

where CollectionID in (select CollectionID from Collection_Rules_SQL where SQL like ‘%Like%’ or SQL like ‘%NOT LIKE%’)

I would highly recommend removing any of these, or using them to initially build a collection, then not updating it, or updating it infrequently.


All content provided on this blog is for information purposes only. Windows Management Experts, Inc makes no representation as to accuracy or completeness of any information on this site. Windows Management Experts, Inc will not be liable for any errors or omission in this information nor for the availability of this information. It is highly recommended that you consult one of our technical consultants, should you need any further assistance.



Contact Us

On Key

More Posts

WME Security Briefing 27 May 2024

Kinsing Hacker Group Exploits Docker Vulnerabilities Overview Recent investigations have shown that the hacker group Kinsing is actively exploiting Docker vulnerabilities to gain unauthorized access to systems. The modified hacker group targets misconfigured Docker API ports deployed with cryptocurrency mining malware.

Read More »
WME Cybersecurity Briefings No. 010
Cyber Security

WME Security Briefing 20 May 2024

Advanced Persistent Threats: North Korean Hackers Launch Golang Malware Overview A new malware strain, called Titan Stealer, is currently actively circulating in the threat landscape, targeting a variety of personal data and linked to North Korean state-sponsored cyber espionage

Read More »
WME Cybersecurity Briefings No. 009
Cyber Security

WME Security Briefing 08 May 2024

Exploitable vulnerability in Microsoft Internet Explorer, used to deploy VBA Malware Overview Cybersecurity researchers discovered a severe exploitation targeting a bug that had already been patched in the Microsoft Internet Explorer browser. Their report added that

Read More »
Be assured of everything

Get WME Services

Stay ahead of the competition with our Professional IT offerings.