Jeevan Bisht's System Center Blog

System Center Rocks !!

SCCM / SMS : Collections not refreshing

SCCM / SMS : Collections not refreshing

  • Comments 2
  • Likes

One of the common issues i have seen over the years when all of a sudden the collections would not refresh and collection membership would not update.You might simply see the hour glass on the collections, Generally nothing was changed on existing collections but a new collection might have been added.

This could generally happend if you have an unoptimized query , querying big tables like 'software files.'


There are multiple ways to look at it including colleval.log with sql enabled logging.


If you have some experience with SQL you could try looking if the Query is a long runner. 

Use the SQL Management Studion and Connect to your SCCM/SMS Database

a) Run the below query on SQL


Find the offending SPID with high CPUTime and DISKIO eg  SPID 67


b) Find the contents of the query

dbcc inputbuffer (67)

this will show the query example below


insert into #CollTemp (MachineID,ArchitectureKey,Name,SMSID,Domain ,IsClient) select  all SMS_R_SYSTEM.ItemKey,SMS_R_SYSTEM.DiscArchKey,SMS_R_SYSTEM.Name0,SMS_R_SYSTEM.SMS_Unique_Identifier0,SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,SMS_R_SYSTEM.Client0
from System_DISC AS SMS_R_System INNER JOIN vSMS_G_System_SoftwareFile AS SMS_G_System_SoftwareFile ON SMS_G_System_SoftwareFile.ClientId = SMS_R_System.ItemKey  INNER JOIN Add_Remove_Programs_DATA AS __System_ADD_REMOVE_PROGRAMS0 ON __System_ADD_REMOVE_PROGRAMS0.MachineID =
SMS_R_System.ItemKey   where (SMS_G_System_SoftwareFile.FilePath like '%C:\pwrpoint.exe%' OR __System_ADD_REMOVE_PROGRAMS0.DisplayName00 = 'SRSQuery')

 Note : The above query is a sample query


C)  Ran the below query to find the collection name based on the above query.

select * from collection_rules where collectionID in (select collectionid from collection_rules_sql where sql like '%<text snippet from step b>%')


select * from collection_rules where collectionID in (select collectionid from collection_rules_sql where sql like '%pwrpoint.exe%')

Remarks % is a wild card in SQL


d) Now you can decide to either modify the query / remove to avoid the problem.


I hope you find this post useful.


Jeevan S Bisht

  • This helped me out today. Thank you!

  • I know this is an old article, but here it goes. Is there a way to create a monitor with SCOM to alert us when we such long running queries?

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment