How to eliminate all updates from SCCM

IMPORTANT This procedure should not be used on any production ConfigMgr system, especially in one with heirarchical configuration. Doing so can cause a mismatch between the different levels and potentially cause lots of pain! This procedure is described here for use in testing and in instances where you are trying to start a software update point (and WSUS) from ground zero. It was originally described here to help in cases where users of WSUS 3.0 (pre-SP1) could recover from client failures caused by importing custom OEM catalogs that exceeded the category limit.

Run the following queries on the SQL database SMS_<yoursitecode>

This one sets all updates to expired
	UPDATE v_Updateinfo SET isTombstoned = 1, isExpired = 1

This one deletes the references to the update metadata
(this query may time out if you have thousands of updates… which will require you to run it many times…)
	DELETE FROM CI_ConfigurationItems
	(SELECT    * FROM  CI_ConfigurationItems AS ci 
	LEFT OUTER JOIN  CI_UpdateInfo AS cui ON ci.CI_ID = cui.CI_ID 
	LEFT OUTER JOIN CI_CICategories AS cicat ON ci.CI_ID = cicat.CI_ID 
	LEFT OUTER JOIN CI_CICategories_All AS cicat_all ON ci.CI_ID = cicat_all.CI_ID 
	LEFT OUTER JOIN CI_CategoryInstances AS catid ON cicat.CategoryInstanceID = catid.CategoryInstanceID 
	LEFT OUTER JOIN CI_CategoryInstances_Flat AS catid_flat ON cicat.CategoryInstanceID = catid_flat.CategoryInstanceID 
	LEFT OUTER JOIN CI_CIHash AS cihash ON cicat.CI_ID = cihash.CI_ID 
	LEFT OUTER JOIN CI_LocalizedProperties AS loc ON ci.CI_ID = loc.CI_ID 
	LEFT OUTER JOIN CI_UpdateCIs AS uci ON ci.CI_ID = uci.CI_ID
	WHERE     (ci.IsTombstoned = 1) AND (ci.IsExpired = 1)) 

This one deletes the references to the content files
	DELETE FROM CI_Contents con
	(SELECT    * FROM CI_Contents con
	LEFT OUTER JOIN CI_ContentFiles as fi on con.Content_ID = fi.Content_ID
	LEFT OUTER JOIN  CI_ContentPackages AS pkg  ON con.Content_ID = pkg.Content_ID 
	WHERE     (ContentHash IS NULL))

Last edited Jan 26, 2010 at 12:03 AM by rhearn, version 2


adelatorre Sep 3, 2011 at 12:52 AM 
Does it matter which order you run these? Can you delete references to the content files before deleting references to the metadata?

adelatorre Sep 3, 2011 at 12:48 AM 
When I try the query that deletes references to the content files, I get:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'con'.

adelatorre Sep 3, 2011 at 12:46 AM 
How will you know if it times out? Do you get an error message? Do I just paste this into the SQL Server Management Studio and click on execute?