SCCM Version Number and CU level
SCCM 1610
Customer has SQL 2012 with SQL Compatibility mode 2012.

Observed behavior:

The behavior is like this:

• We have a collection A (BCS026B0)that contains 80.000 objects (or 100.000). It is big.
• If we create a new collection B that is limited to A, once the update cycle for the collection B the SQL query will run with the ;WITH LimitedBaseTable as
• This will generate a blocking and will never end.
The above behavior is not always reproducible with all big collections.

What we think would be useful is to add query timeout for this kind of scenarios (Big Limiting collections )

We figured out that if you create a time consuming/complex query for a collection which runs very long, the whole SQL server is crashing after a few hours.
The query can be designed within SCCM console, but seems to be too complex for the system.
The result is that the collection evaluation takes too long and is not terminating after some time.
All other collections evals will wait for this to complete. DUe to the fact that this takes too long, all others will get queued in tempdb.
The result ist that the tempdb grows and grows until the whole disk space is used.
The result is that the server crashes.

The only solution ist to restart SQL server which will cleanup tempdb and the disk space used.
After collection eval is running again it is fine until those blocking collection gets affected.
Then you will have the same situation as before.

In our opinion we are talking about a not acceptable situation/bug, because a simple query can crash the whole system. In the end no software assignment or OSD will work.