The following is a guidline to resolving CI replication issues – If you are unsure then don’t change things without asking for advice!
Summary of steps:
• Backup the site and the DB.
• Uninstall the SUP
• Delete data from CI_configurationitems and CI_sdmpackage tables so you have 0 rows in the first table and 12 schema rows in the CI_Sdmpackages table.
• Clean the objreplmgr.box of old files.
• Rename the objreplmgr.dll to objreplmgr.dll_old (stop and start services to do this).
• Wait for the files to replicate from the Central site (the .SHA file will disappear when this has finished).
• Rename the objreplmgr.dll and wait for the replicated files to be processed.
1. Backup the SCCM database! If anything goes wrong with the actions below then we can roll back to the latest backup.
2. Uninstall SUP on the primary site. This would trigger all the updates to get marked as expired in the SCCM DB. The following queries can be checked to see when that’s been done and to track the progress, however this may take some time:
SELECT COUNT (*) AS TotalCIs FROM CI_ConfigurationItems
SELECT COUNT (*) AS Expired FROM CI_ConfigurationItems WHERE IsExpired = 1
SELECT COUNT (*) AS TotalSDMs FROM CI_SDMPackages
SELECT COUNT (*) AS Deleted FROM CI_SDMPackages WHERE IsDeleted = 1
3. After all the CI items have been marked as isexpired then modify the following property in Site Control file, and change the value to 0 instead of 7 days. You will need to stop the smsexec service to do this:
PROPERTY Updates Cleanup Age 0
4. Restart SMS Executive Service. WSUS_SYNC_MANAGER will now start deleting the Expired updates, and log entries similar to below in WSyncMgr.log:
Deleting old expired updates… SMS_WSUS_SYNC_MANAGER 4/23/2010 4:18:52 PM 4104 (0x1008)
Deleted 100 expired updates SMS_WSUS_SYNC_MANAGER 4/23/2010 4:19:22 PM 4104 (0x1008)
Deleted 2995 expired updates total SMS_WSUS_SYNC_MANAGER 4/23/2010 4:28:48 PM 4104 (0x1008)
5. After all the Expired updates have been deleted, check that the vast majority of the CI_SDMPackages are marked as “isdeleted” using the select count (*) queries above. There are 12 rows at the top of this table which must never be deleted. These are usually marked as NULL in the source site column. To delete the “isdeleted” SDM packages run the following Stored Procedure to remove them:
exec sp_DeleteOldSDMPackageData 0
6. After these procedures have been run we should now have two clean tables. Perform a select * from CI_ConfigurationItems / CI_SDMPackages to check the contents and confirm that these tables are clean. There should now only be about 0 rows in the Ci_configurationitems table and 12 in the sdmpackages table.
** You may have to deal with constraints on the tables by disabling and enabling if you cant clean the tables out and get the error about constraints**
7. The next step is to clean out the existing files in the objmgr.box. Probably the best thing to do is to archive all the current data to an alternate location and clear out any files from the objmgr.box and the subfolders to make sure there is no old data lurking here.
8. Once we have cleaned the objmgr.box rename the objreplmgr.dll file in Program Files (x86)\Microsoft Configuration Manager\bin\i386 to objreplmgr.dll_old (you will have to stop and start the smsexec to do this).
This will prevent the processing of the files sent from the central site until the replication has completed. This is necessary so that we ensure that some of the files retry period does not go “bad” before the files have had time to arrive.
9. Reinstall the SUP role on the primary site. This should trigger the replication of the CI objects automatically. If this doesn’t replicate immediately then create a .sha file in the objmgr.box on the central site.
10. Wait for the replication process to stop transferring files from the central site. This can sometimes take days and sometimes hours depending on the number of CI objects and WAN link speed. Once you have confirmed that this has stopped then stop the smsexec process on the primary site and rename the objreplmgr.dll_old to objreplmgr.dll. Restart the smsexec service.
11. This processing of the files in the objmgr.box\INCOMING folder should now begin. Hopefully this should complete successfully without any further insertion errors.
12. Change the following property in Site Control file back , from 7 instead of 0 days. You will need to stop/start the smsexec service to do this:
If whist replicating all the CI objects do not replicate the most likely they have an issue with transaction numbers being out of sync
Some queries which i used
select COUNT (*) as totalcis from CI_ConfigurationItems where IsExpired= 0
select COUNT (*) from CI_ConfigurationItems
select COUNT (*) as expired from CI_ConfigurationItems where IsExpired= 1
select * from CI_ConfigurationItems where IsExpired= 0
select COUNT (*) from CI_SDMPackages where IsDeleted= 1
exec sp_DeleteOldSDMPackageData 0
select * from CI_ConfigurationItems
select * from CI_SDMPackages
delete from CI_SDMPackages where SDMPackage_ID > 17906
select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_NAME= CI_SDMPackageRelations_CI_SDMPackages_FK
select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_NAME= CI_SDMPackageRelations
select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_NAME= ‘CI_SDMPackageRelations_CI_SDMPackages_FK’
ALTER TABLE CI_SDMPackageRelations NOCHECK CONSTRAINT ALL
ALTER TABLE CI_SDMPackageRelations WITH CHECK CHECK CONSTRAINT ALL
ALTER TABLE CI_SDMPackages WITH CHECK CHECK CONSTRAINT ALL
SMS: Replication Manager Cannot Process Transactions If Transaction ID Is Not Synchronized
Understanding Site to Site Communication in SMS/SCCM
Troubleshooting “Failed to Insert Object” error message