ConfigMgr 2007 CI replication issue

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.

Detailed steps:

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**
http://www.confio.com/logicalread/sql-server-foreign-keys-some-of-the-mystery-explained!-(part-2)/#.UsKiJ2ZFBEY

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:

PROPERTY

If whist replicating all the CI objects do not replicate the most likely they have an issue with transaction numbers being out of sync
http://blogs.technet.com/b/configurationmgr/archive/2012/04/17/a-look-at-transaction-based-replication-in-configuration-manager-2007.aspx

———————————————————————————-
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
GO

ALTER TABLE CI_SDMPackages WITH CHECK CHECK CONSTRAINT ALL
———————————-
References
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

http://blogs.technet.com/cfs-filesystemfile.ashx/__key/CommunityServer-Components-PostAttachments/00-03-34-44-65/ConfigMgr2007Site2SiteReplication.doc

Problem SCCM Records DDR

Problem SCCM Records- http://nikifoster.wordpress.com/2012/05/25/problem-sccm-records/

So I was having a discussion with a colleague today about records in SCCM and the different problems we have (or I have previously had) with them!

There is quite a bit of information on the net regarding records but a lot seem to be a bit misleading, probably due to the small amount of official Microsoft information, as well as the wording being a inaccurate.

If you’re reading this, then chances are you’ve read this – http://elmunjo.blogspot.com/2010/01/managing-conflicting-records-and.html – and although this is good info (I’ve copied some of it in to this post) I actually believe it is only part correct. Specifically, I think the “conflicting records” section is incorrect.

This post is an attempt to clarify the problems and I have classified them into 3 categories, based on my own experiences. I thought it important to seperate the different types of records due to the different behaviour that occurs with each one. If you think you have something to add or think I have something wrong here, please feel free to comment and I will try and integrate it into the article.

Conflicting Records
Defined as: Records that have either the same or different name, different GUID and the same hardware ID.

This is normally caused by rebuilding the exact same machine, that already has a record in SCCM, with the same name. In this instance a second record appears in SCCM, with the same name and the same hardware ID.

You can configure SCCM to deal with these types of records automatically ( but that brings its own set a challenges! In this case the old record is marked as obsolete and deleted according to the Delete Obsolete Client Discovery Data Maintenance Task (Default= 7 days). If the site is not configured to handle conflicting records (Site Properties/ Advanced), both records are shown under the conflicting records node in the ConfigMgr console and the Administrator has to manually choose the method for managing these records. It can also be scripted, check out this excellent post for more info – http://ccmexec.com/2011/03/merging-obsolete-records-in-configuration-manager-2007/

Duplicate Records
Defined as: Records that have the same name, different GUID and different hardware ID.

This is normally caused by a machine, after already having a record in SCCM, having its hardware ID changed and so getting a new GUID. Circumstances that trigger generation of a new hardware ID:
1.Some aspects accountable for the Hardware ID have changed.There‘s 10 criteria monitored for Desktops, if 3 of them change, the ID also changes. For Notebooks 2 of 7 criteria have to change.
2.The SMBios serial number has changed
3.The Computer SID has changed

So for example, if you image a machine, then remove the HDD and place into another machine, even of the same type, the machine ID will change, a new GUID will be generated and you will see a duplicate record in SCCM.

There are also other scenarios where you will see duplicate records, for example: http://blogs.technet.com/b/configmgrteam/archive/2011/09/09/known-issue-and-workaround-duplicate-records-when-you-use-unknown-computer-support-with-active-directory-delta-discovery.aspx

How to deal with these? Well you can either delete them or script it to merge them. I’m afraid I dont have a script you can use but I do know the script used to deal with conflicting records WON’T WORK!

Duplicate GUIDs
Defined as: All records with the same GUID

In this case multiple machines use the same record in the ConfigMgr database. Duplicate GUIDs occur when:
1.Harddisks are duplicated with installed SCCM Client
2.Computers are renamed with installed SCCM
3.Client Computers are configured to dualboot, using the same PC name and having the SCCM Client installed in both configurations.

Clients, that share one database record cannot be managed correctly. A collection will always show the client with the most recent discovery record and that means the machine corresponding to the collection member continiously changes. This means software (or even OSs) meant for one computer could potentially be advertised to a completely different one. Not good!

SCCM offers a report to show the conflicts, but does not offer any means of remediation, the admin is forced to use other methods. There is a whitepaper from Microsoft for handling duplicate GUIDs with SMS 2003 which is still valid: http://www.microsoft.com/downloads/details.aspx?FamilyID=aaf6f10d-bd84-405e-9af3-b48ced1d7f2d&DisplayLang=en

References
http://anoopcnair.com/2011/04/08/configmgr-sccm-duplicate-record-issue/

http://blogs.technet.com/b/configmgrteam/archive/2011/09/09/known-issue-and-workaround-duplicate-records-when-you-use-unknown-computer-support-with-active-directory-delta-discovery.aspx

http://ccmexec.com/2011/03/merging-obsolete-records-in-configuration-manager-2007/

http://elmunjo.blogspot.co.uk/2010/01/managing-conflicting-records-and.html

Indexes

http://donnietaylor.wordpress.com/2012/04/20/configmgr-and-sql-handy-tips-tricks-and-scripts/
From the above blog

http://blogs.technet.com/b/smartinez/archive/2014/03/28/talking-database-in-configmgr.aspx

SQL is an every-day part of a ConfigMgr’s life. Below is a small set of scripts we have used in production on ConfigMgr 2007 for quite a while. Standard disclaimer: Test everything first.

Indexes:

SQL server uses statistics to keep track of values in an index, and determine when and how to use that particular index while processing a query. This is a horribly simplified definition (because I barely understand it), but basically it means that statistics are a way for SQL to find the best index to use. By default when you create a database in SQL 2005 (such as the ConfigMgr Database), the Auto Update Statistics option is turned on. You can check it by opening SQL Management Studio, right click on the database, select properties, then select the Options.

Now that you know what they are, it’s important to know when to manually kick off an update to the statistics. There are times when the key values in an index will change – especially in the ConfigMgr database. Patch Tuesday, for example – there is a lot of new data flooding the ConfigMgr and WSUS databases as clients scan and report back patch status. After large distributions also change a large amount of data in the indexes (status from distribution and advertisements).

Auto-Update of Statistics will catch these changes, but there will be times when you want your queries to execute at their fastest without waiting for the system task to kick off. There are also times when the system task will take a lower priority to other tasks, effectively keeping your statistics out of date. When you need to update the stats on index manually, use the following command:

UPDATE STATISTICS TABLENAME –replace Tablename with appropriate table

This works great on a single table, but who wants to do that for an entire database? Use the built-in stored procedure to update all statistics on all indexes in your database. Be aware that this can take some time, and if you don’t have Async Auto Update Statistics on, could cause queries to time-out while it’s running.

/******Code Below Here******/

USE ConfigMgr –change to the name of your database

EXEC sp_updatestats

/******Code Above Here******/

We use this on a set schedule, every 12 hours, to keep our stats update to date, and avoid any priority problems with the auto-update process. This does have an impact on indexes, so be sure you test accordingly.

Fragmentation:
From MS

Exec sp_MSForEachtable ‘DBCC DBREINDEX (”?”)’
go

Exec sp_MSForEachTable “UPDATE STATISTICS ? with fullscan”
Go

Or

If you suspect that you have fragmented SQL indexes in your ConfigMgr database then what are your options? You could wait for your “Rebuild Indexes” ConfigMgr maintenance task to come around again, or you could just go ahead and rebuild those indexes quickly from SQL Management Studio.

If you want to rebuild all indexes in the ConfigMgr database, which is quite a lengthy and process consuming task, you can run this query. Keep in mind that this is an intensive operation, so it’s best to do it off hours. Expect it to take quite a while to complete.

/********************CODE BELOW HERE*************/

USE SCCM –Enter the name of the database you want to reindex

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR

SELECT table_name FROM information_schema.tables

WHERE table_type = ‘base table’

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0

BEGIN

DBCC DBREINDEX(@TableName,’ ‘,90)

FETCH NEXT FROM TableCursor INTO @TableName

END

CLOSE TableCursor

DEALLOCATE TableCursor

/********************CODE ABOVE HERE*************/

But what if you just want to rebuild a single index? First, you need to know the name of the index. You can find that out a variety of ways, including just looking directly at the table in SQL Management Studio – there is a sub-folder per table for indexes.

Once you have the index name, you can run this quick statement to just rebuild that particular index:

/********************CODE BELOW HERE*************/

DBCC dbReindex(‘INDEX_NAME_GOES_HERE’,’ ‘,90)

/********************CODE ABOVE HERE*************/

BTW, the 90 in both queries is a fill factor. Typically you won’t have to change that. A useful tip page can be found here:

http://www.mssqlcity.com/Tips/tipSrvSet.htm

While working on a performance problem with a couple of very talented SQL gurus I was handed this script. It checks, among other things, the fragmentation of the indexes in the ConfigMgr database. This will help tell you if your rebuild indexes task is being run often enough, or if you need to target specific indexes more often with an additional SQL Task.

**************CODE BELOW HERE********************

SELECT * FROM sys.dm_db_index_physical_stats

(DB_ID(N’ConfigMgr’), NULL, NULL, NULL , ‘DETAILED’)

order by 9 desc;

GO

*************CODE ABOVE HERE**********************

Be sure you change the “ConfigMgr” above to the name of your database!!

This is going to return quite a few indexes, and if you check the 9th column (avg_fragmentation_in_percent), you can see how badly they are torn up. Now, before you get too upset that most of them read 100%, keep in mind the Page_Count column. If an index only has 5 pages, and it shows 100% fragmentation, then that is not really that big of a deal. It just means that those 5 pages aren’t in order. If, however, you see an index with 20,000 pages and it shows a high fragmentation percentage….well, then you can be sure that you aren’t getting all of the performance you can from your SQL database.

If you need to find out what index has a high fragmentation – check out the 2nd column. Object_ID. Note the object_id and run this query:

*********CODE BELOW HERE***************

SELECT OBJECT_NAME(OBJECT_ID)

FROM master.sys.objects

*********CODE ABOVE HERE****************

Be sure you change the “OBJECT_ID” above to the appropriate ID you want to query!!

This will return the ‘common’ name for the index, and should give you a good idea what table it’s attached to.

So, keep in mind that the 9th column – avg_fragmentation_in_percent – will show 100% for quite a few indexes….but the page count on those indexes should be low. If you find an index with a high number of pages, and high fragmentation percent, then consider running your Rebuild Indexes task more often, or target specific indexes with a SQL task.

Generic Space:

Ever wonder what is taking up all the space in your ConfigMgr database? This SQL query will show row count, reserved/used data size, and reserved/used index size.

This code works for any database, not just ConfigMgr. Enjoy!

**********************CODE BELOW HERE*************************

declare @id int

declare @type character(2)

declare @pages int

declare @dbname sysname

declare @dbsize dec(15,0)

declare @bytesperpage dec(15,0)

declare @pagesperMB dec(15,0)

create table #spt_space

(

objid int null,

rows int null,

reserved dec(15) null,

data dec(15) null,

indexp dec(15) null,

unused dec(15) null

)

set nocount on

– Create a cursor to loop through the user tables

declare c_tables cursor for

select id

from sysobjects

where xtype = ‘U’

open c_tables

fetch next from c_tables

into @id

while @@fetch_status = 0

begin

/* Code from sp_spaceused */

insert into #spt_space (objid, reserved)

select objid = @id, sum(reserved)

from sysindexes

where indid in (0, 1, 255)

and id = @id

select @pages = sum(dpages)

from sysindexes

where indid < 2

and id = @id

select @pages = @pages + isnull(sum(used), 0)

from sysindexes

where indid = 255

and id = @id

update #spt_space

set data = @pages

where objid = @id

/* index: sum(used) where indid in (0, 1, 255) – data */

update #spt_space

set indexp = (select sum(used)

from sysindexes

where indid in (0, 1, 255)

and id = @id)

– data

where objid = @id

/* unused: sum(reserved) – sum(used) where indid in (0, 1, 255) */

update #spt_space

set unused = reserved

– (select sum(used)

from sysindexes

where indid in (0, 1, 255)

and id = @id)

where objid = @id

update #spt_space

set rows = i.rows

from sysindexes i

where i.indid < 2

and i.id = @id

and objid = @id

fetch next from c_tables

into @id

end

select top 25

Table_Name = (select left(name,30) from sysobjects where id = objid),

rows = convert(char(11), rows),

reserved_KB = ltrim(str(reserved * d.low / 1024.,15,0) + ‘ ‘ + ‘KB’),

data_KB = ltrim(str(data * d.low / 1024.,15,0) + ‘ ‘ + ‘KB’),

index_size_KB = ltrim(str(indexp * d.low / 1024.,15,0) + ‘ ‘ + ‘KB’),

unused_KB = ltrim(str(unused * d.low / 1024.,15,0) + ‘ ‘ + ‘KB’)

from #spt_space, master.dbo.spt_values d

where d.number = 1

and d.type = ‘E’

order by reserved desc

drop table #spt_space

close c_tables

deallocate c_tables

**********************CODE ABOVE HERE*************************

Open Transactions:

Open transactions – transactions that run too long or are hung – can cause havoc on the ConfigMgr database. Notice a backlog of files (DDRs or Mifs)? Slow processing in general? Collections having a problem updating? You might want to give this a quick check.

First, lets find out what the oldest transaction on the ConfigMgr database is. Open SQL Management Studio, and start a new query. Change the focus to your SCCM database,and run this command first:

DBCC OPENTRAN

Did any transactions come back? If so, check their start time and make sure they aren’t too long in the past. Anything past a couple of minutes, except for the largest of queries, would be unacceptable. Note the Process ID – we will use that next.

So now we know what the oldest transaction is, but what do we do with this info? Let’s see what that transaction is doing. Run this command next:

DBCC INPUTBUFFER()

You will see a snippet of the code that the process is running. Does this help track down what the open transaction is? Perhaps a long-running query rule for a collection, or a site maintenance task that is hung. Typically you can get a decent idea what it is by examine the output of Inputbuffer.

Now that you know what is causing the problem, how do you deal with it? Well, if you are sure that you want to stop this transaction, you do it easily with one more command. Use it with caution!

kill

Note that if is sometimes helpful to do these same steps on the tempdb of the SQL server the ConfigMgr database sits on…especially for long running transactions.