As I type, I'm in the middle of a major Configuration Manager problem. You can't do too many updates deployments when every, single update in the database is expired! This is what it took to get my SCCM software updates back up and going after a restore.

According to my Microsoft support person, this isn't the first time this had to be done after a restore so beware SCCM admins! I hope you never have to go through this.

What I'm about to explain is in no way my fault if it hoses your database. This was my solution for fixing my updates were all were expired in the database and the WSUS sync was failing with CCISource error: 2. From my experience, this error code always has to do with some kind of corruption in the SCCM database.

Apparently, I was still able to see the updates only because the metadata was still available so the updates were long gone anyway. I was hoping I didn't have to go through an entire re-sync again and setup all update groups, packages and deployments but alas that was a false hope.=

Here are the steps that Microsoft support and I went through to get my ConfigMgr software updates back up and going again:

  1. Kicked off the SMS_SITE_BACKUP component to start a backup, monitored via the smsbkup.log file and confirmed success.
  2. Ran SQL query

select * from CI_ConfigurationItems

  1. Found the ModelName attribute by looking in the wsyncmgr.log file and looked for the line Referenced configuration items are not available yet:.. On this line, you'll find a ModelName=Site_ABCDEF/SUM_XYZ line. You need the site model name which in this example is ABCDEF.
  2. Ran SQL queries to find all affected updates

SELECT * FROM CI_DocumentStore WHERE Document_ID NOT IN (SELECT Document_ID FROM CI_CIDocuments)
SELECT * FROM v_updatecis WHERE ModelName NOT LIKE '%ABCDEF/SUM%'

Stopped the SMS_EXECUTIVE service but, in my case, it didn't not stop so the smsexec.exe process was killed.

  1. Stopped SMS_SITE_COMPONENT_MANAGER service
  2. Ran SQL query to find no rows affected

UPDATE CI SET IsExpired=1 FROM v_updatecis ci where ci.citype_id in (1,8) and modelname like 'Site_ABCDEF/SUM_%"

  1. Started the SMS_EXECUTIVE service again.
  2. Removed the software update point role
  3. Checked SUPsetup.log on SUP server to confirm role removal
  4. Removed the Windows Server Update Services role and all subfeatures from the SUP server and rebooted
  5. Ran SQL queries to remove all expired updates
update ci_configurationitems set isexpired=1 where citype_id in (1,8)
delete Ci_configurationitemrelations where toci_id in (Select ci_id from ci_configurationitems where citype_id in (1,8))
delete ci_assignmenttargetedCIs where ci_id in (Select ci_id from ci_configurationitems where citype_id in (1,8))

The first UPDATE query usually takes a little bit and in my case actually failed with the error: delete statement conflicted with the reference constraint "ci_currenterrordetails_ciid_fk The conflict occurred in the database...
It looks like CIType_Id 8 was causing the foreign key constraint because

delete ci_configurationitems where citype_id = 1 worked on it's own.

A SQL engineer took over and create a temp stored procedure to correct the error. Afterwards, the database was clean!

  1. Renamed the SMS, UpdateServicesPackages and WsusContent WSUS folders to .old on the server holding the software update point role
  2. Reinstalled WSUS on the SUP server with the same content directory as before
  3. Run WSUS to perform the post-installation tasks
  4. Added the SUP role back again documenting all checked categories
  5. Unchecked all categories but a couple to speed up the initial sync
  6. Confirmed a successful software update point role install in SUPsetup.log
  7. Checked wsusctrl.log to ensure the SUP role is connecting to the WSUS server
  8. Go to the WSUS console -> Options -> Products and Classifications and uncheck the all but a couple categories to match ConfigMgr categories
  9. Ensure the UpdateServicesPackages and WsusContent folders have recreated on the SUP role server
  10. Started a manual ConfigMgr WSUS sync
  11. Removed all update groups, packages and deployments
  12. Checked wsyncmgr.log and sync was successful and updates are showing up correctly in the console
  13. Checked all product categories needed again and initiated another sync to get the rest of the updates into the database.
  14. Recreated all update groups, deployment packages and deployments.

Join the Jar Tippers on Patreon

It takes a lot of time to write detailed blog posts like this one. In a single-income family, this blog is one way I depend on to keep the lights on. I'd be eternally grateful if you could become a Patreon patron today!

Become a Patron!