Jeevan Bisht's System Center Blog

System Center Rocks !!

SCCM / SMS : Collections not refreshing

SCCM / SMS : Collections not refreshing

  • Comments 1
  • 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.

http://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

  • This helped me out today. Thank you!

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