“The site database has a backlog of SQL change tracking data”

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

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

Connect to SQL

2 . Create a new Database Engine Query

Create a new Database Engine Query

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

Congratulations, you have made a DAC!

Connection prefixed with ADMIN:

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

Query should look similar to this

5 . Click Execute

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

Enter the stored procedure

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!

Nothing happened

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.

is change tracking auto cleanup on?

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.

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

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

I re-ran the SCCM Update prerequisite check…

Prerequisite passed!

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

After upgrade to 1906, CT data is 6 days old
After running the Stored Procedure, the tables were cleaned successfully
spDiagChangeTracking changes better accounts for failed cleanups

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

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

Leave a comment

Your email address will not be published.

Time limit is exhausted. Please reload CAPTCHA.

 

This site uses Akismet to reduce spam. Learn how your comment data is processed.

2 thoughts on ““The site database has a backlog of SQL change tracking data””