How to eliminate all updates from SCCM

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 Feb 11, 2009 at 7:51 PM by rhearn, version 1


No comments yet.