Had some problems with loads of collection refresh taking all of the CPU on the SCCM-server.

So first, to get the SCCM-server to calm down I wrote a small (and somewhat ugly, since it uses SQL) hack:

UPDATE Collections
Set Flags = 17
Where CollectionName LIKE '%Something In The Collection Name%'
AND Flags=18

This script uncheck the box “Update his collection on a schedule” for the collections.

Then, when the SCCM server did go back to a normal CPU-utilization we used this script to set another refresh-rate on the collections.

Const cSccmProvider = "."
Const cWmiUsername = ""
Const cWmiPassword = ""

Const cCollectionNamePattern = "%Something In The Collection Name%"
Const cDoUpdate = True          ' Set to false to test
Const cRefreshDays = 0          ' 0 - 31
Const cRefreshHours = 12        ' 0 - 23
Const cRefreshMinutes = 0       ' 0-59

Set oLocator = CreateObject("WbemScripting.SWbemLocator")

' --- Get SCCM Site Code
WScript.Echo "Connecting to: " & cSccmProvider
Set oSccmWmi = oLocator.ConnectServer(cSccmProvider, "root\sms", cWmiUsername, cWmiPassword)
Set oWmiQuery = oSccmWmi.ExecQuery("SELECT SiteCode FROM SMS_ProviderLocation WHERE ProviderForLocalSite=true")
For each currentSite in oWmiQuery
        sSccmSiteCode = currentSite.SiteCode
        Exit For
Next

' --- Connect to site
WScript.Echo "Connecting to: " & cSccmProvider &  " - root\sms\site_" & sSccmSiteCode
Set oSccmWmi = oLocator.ConnectServer(cSccmProvider, "root\sms\site_" & sSccmSiteCode, cWmiUsername, cWmiPassword)

' --- Create interval
WScript.Echo "Creating Interval: " & cRefreshDays & " days, " & cRefreshHours & " hours, " & cRefreshMinutes & " minutes."
Set oInterval = oSccmWmi.Get("SMS_ST_RecurInterval")
oInterval.DaySpan = cRefreshDays
oInterval.HourSpan = cRefreshHours
oInterval.MinuteSpan = cRefreshMinutes
oInterval.isGmt = False
oInterval.StartTime = "20090101000000.000000+***"

' --- List all collection
set oCollections = oSccmWmi.ExecQuery("SELECT * FROM SMS_Collection WHERE Name LIKE '" & cCollectionNamePattern & "'")
For Each oCollection In oCollections
        ' --- Update interval on Collection
        If cDoUpdate Then
                WScript.Echo "Updating: " & oCollection.CollectionID & " - " & oCollection.Name
                Set oCollectionToChange = oSccmWmi.Get("SMS_Collection.CollectionID='"  & oCollection.CollectionID & "'")
                oCollectionToChange .RefreshSchedule = Array(oInterval)
                oCollectionToChange .RefreshType = 2  '1 = Manual, 2 = Periodic refresh
                oCollectionToChange .Put_
        Else
                WScript.Echo "Testing: " & oCollection.CollectionID & " - " & oCollection.Name
        End if
Next

The script is attached here: changeCollectionRefresh.vbs