SCCM / SMS : Collections not refreshing

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.

https://technet.microsoft.com/en-us/library/bb892800.aspx

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

sp_who2

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>%')

ex.

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.

regards

Jeevan S Bisht

Comments

• Anonymous
  May 30, 2012
  This helped me out today. Thank you!

• Anonymous
  October 16, 2014
  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?