SOLVED Hourglass on device collections(in CAS) doesn't disappear

[Sungpill Han]

I have 1 CAS and 2 Primary Site servers.
This issue happened in the past and rebooting servers have fixed, but not anymore.
When I execute Update Membership on device collections from CAS, hourglass doesn't disappear. The collection updates membership correctly, it's just the hourglass never goes away.
As far as I understood, collection evaluation is executed from primary site servers with db. When I update memberships from primary site servers, collection evaluation finishes successfully and hourglass disappears as expected. However, when I do from CAS server, hourglass doesn't disappear. Looking at colleval.log log files from both primary site servers, collection evaluation finishes with no issue. It's just CAS still showing hourglasses for almost 3 days now.
I tried;
- reboot two primary site servers and cas server, didn't fix
- examined colleval.logs in primary site servers, no error or issue. Evaluation finishes on time and membership updates successfully.
- Monitoring> Database Replication> Replication Link Analyzer shows no errors or warnings, all green check marks.
- SQL service account at CAS has SA permission, ownership of site databases of two primary site servers. No SQL issue.
- collections are limited to All Systems which is not changeable (grayed out ) and it's set to incremental.
I tried to change limited collection to a different one and take out both incremental and full, it's still showing hourglass. Again, the collection updates are successfully done as colleval.log show on both primary site servers and numbers updated as well in CAS, it's just the annoying hourglass icon doesn't disappear.
Environment:
1 CAS and 2 Primary site servers : SCCM 1702

Updated:
I think I found the cause of hourglass on CAS.
Collections with hourglass have collection update history of removed sites (Site 3,4) and collections with no hourglass have no history of removed sites in Table dbo.Collections_L.

Please, advise how to change table to read-write and delete stale records. It seems as 'tr_Collections_L_NonUpdatable_INS_UPD_DEL' trigger can be disabled temporarily and the stale records can be deleted. But I like to know if it's safe to do this way, currently I have no knowledge how SQL trigger works, will read about it...

 

[Garth]

It is not supported to edit the database. You will need to contact Microsoft support (CSS) directly for this issue. Since you have a CAS, you likely have a onsite Microsoft SCCM person too. They would be a great person to talk to too.

 

[Sungpill Han]

Garth,
I'm the one manages CAS and two PSS.
I contacted MS and haven't gotten the answer, yet. I understand the risk of modifying the database directly, like to know if I want to modify the table in the database, wonder if disabling the trigger would let me remove the stale records.
If anyone has experience of unlocking and modifying read-only SCCM tables, please let me know.

 

[Garth]

Because you have over 150,000 computers, this is extremely dangerous, particularly since all data is replicated between primary servers. Modifying one table on one CAS/Primary and not the others can cause other sync issues. This is no something you should do without MS support.

 

[Sungpill Han]

Thank you for advice.

 

[Sungpill Han]

I just gave it a shot.
1. Disabled tr_Collections_L_NonUpdatable_INS_UPD_DEL
2. Delete from Collectgions_L where CollectionID ='58' and (SiteNumber=3 or SiteNumber=4)
As soon as I did, the hourglass on Collection 58 disappeared and any subsequent Membership Update from CAS server finishes successfully and hourglass disappears in 2 mins.
Problem solved.
I don't understand why I have to remove stale records manually. Configuration Manager Wizard for site removal has left stale records on multiple places in database even though the removal process says successful with all green checkmarks, Component Status windows was also one of them.
For this collection update issue, it was similar as Component Status window, table Collections_L had old removed sites and CAS seems as it was waiting for the collection update result from the removed sites, so it shows hourglass on GUI console. but, of course, she will not receive the data. Removing the stale records of sites from the table fixed the issue.
I was not able to delete the stale records because the table is read-only meaning there's a trigger preventing insert,update,delete command. I was able to right-click the trigger and disable temporarily and delete the state records, then re-enabled the trigger.
I think Microsoft has to review site removal feature of setup.exe, it doesn't seem to check if the database table is protected by trigger or no. Maybe no communication between SQL team and setup.exe developer???

 

[Garth]

Microsoft will not know about the problem, if someone doesn't open a support case. For everyone who find this post remember the editing the database like this is completely unsupported.

 

[Prajwal Desai]

Agree with what @Garth says. Do not modify anything in the DB, it is better to log a support case with Microsoft.