"The site database has a backlog of SQL change tracking data"

One of the more common prerequisite warnings I see when updating SCCM in my lab is:-

[Completed with warning]:The site database has a backlog of SQL change tracking data. For more information, see https://go.microsoft.com/fwlink/?linkid=2027576

SCCM Update Pack Installation Status

We can see the same warning in ConfigMgrPreReq.log

What does this warning mean?

Starting in SCCM 1810, the in-console update wizard now performs a check to see if the site database has a backlog of SQL change tracking data.

At times of replication or heavy data processing, the oldest value in the syscommittab table can be over 5 days old (SCCM Default value is 5 days). Microsoft recommend that you clean up any change tracking data over 7 days old.

To verify the prereq warning, we can connect to our SQL Database with a Dedicated Administrator Connection (DAC) by using SQL Server Management Studio (SSMS) or the SQLCMD command line.

We will use SSMS in the following example to check the age of our change tracking data.

Make a Dedicated Access Connection to SQL via SSMS

The DAC doesn’t allow multiple connections and by its very nature, this is what the Object Explorer in SSMS does. Follow the steps below to make a DAC.

1 . Open SSMS and connect to your SCCM Database engine

2 . Create a new Database Engine Query

3 . Modify the connection and prefix the Database with ADMIN:

Congratulations, you have made a DAC!

4 . In the DAC query window, enter the following query.

USE <ConfigMgr database name>
EXEC spDiagChangeTracking

5 . Click Execute

The Stored Procedure executes. In the results below the CT_DAYS_OLD value is 18

The oldest change tracking data in the syscommittab table for SCCM should be 5 days and Microsoft recommend cleaning up data older than 7 days. 18 days has over stepped the mark somewhat and triggered the warning for the SCCM update prerequisite.

How do we clean up old change tracking data?

If the auto-cleanup task isn’t cleaning up old change tracking data we can clean it up using another builtin stored procedure. Reference https://docs.microsoft.com/en-us/sccm/core/servers/deploy/install/list-of-prerequisite-checks#bkmk_changetracking

1 . Using the same DAC Query window, run the following stored procedure

EXEC spDiagChangeTracking @CleanupChangeTracking = 1

2 . Click Execute

So that’s it right? It would be my expectation that CT_Days_Old value would decrease. If yours did..job done, re-run the SCCM prerequisites check and enjoy the green ticks. End of Blog Post…….

Digging Deeper

Didn’t work? Take a look below. My syscommittab table still has change tracking data that is 18 days old in my lab environment!

So i sat scratching my head. Lets look to see if auto cleanup is enabled for our database. Kendra Little over at @BrentOzarULTD Blog Page has an excellent blog post if you want to find out more https://www.brentozar.com/archive/2014/06/performance-tuning-sql-server-change-tracking/
I am using a query from the blog post to look to see if change tracking is enabled on my database, if auto cleanup is enabled and what the retention period for tracking changes is.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
SELECT
db.name AS change_tracking_db,
is_auto_cleanup_on,
retention_period,
retention_period_units_desc
FROM sys.change_tracking_databases ct
JOIN sys.databases db on
ct.database_id=db.database_id;
GO

As we can see, my SCCM database does have auto cleanup on and the retention period is 5 days! So whats going on?

The auto-cleanup should be running every 5 days…but hang on, my lab is on my laptop which is switched on intermittently - normally for testing new updates and features. Maybe i need to give the engine room more time to do its thing.

I tested a theory, totally unsupported BTW. I changed the retention period to 1 hour and went to cook dinner.

ALTER DATABASE CM_BB1
SET CHANGE_TRACKING (CHANGE_RETENTION = 1 Hours)

When i returned, the auto-cleanup had done its thing!

I then set the retention period back to the default of 5 days

ALTER DATABASE CM_BB1
SET CHANGE_TRACKING (CHANGE_RETENTION = 5 days)

I re-ran the SCCM Update prerequisite check…

Conclusion

I don’t fully understand yet why running the manual stored procedure didn’t work, it just didn’t handle the cleanup at all.
UPDATE In SCCM 1906 the Stored Procedure spDiagChangeTracking has been updated (5.0.8853.1006) and I have been reliably informed that the SP now performs the cleanup!

Now trying the SP in 1906

Thanks @GarthMJ @SqlBenjamin @AdamGrossTX for your help with this one.

Let me know if you have anything to add on this topic! :)