“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! 🙂

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

  1. This is a SQL Server 2014 SP3 issue, but it had been driving me crazy until CU4 was released a few weeks ago.

    One of my customers was having outages that we traced to slowness/blocking in the “syscommittab” table.

    That table did not seem to exist. I found it was an internal table that could be viewed thru the dynamic view “sys.dm_tran_commit_table”
    select top 10 * from MyDatabase.sys.dm_tran_commit_table order by commit_time
    Showed records much older than the retention_period.

    When I ran
    EXEC sp_flush_commit_table_on_demand
    It did not clean anything and showed 2 functions returning old values
    change_tracking_hardened_cleanup_version()
    safe_cleanup_version()

    I opened a premier support ticket with Microsoft and they could not find any mention of this problem.

    I researched Change Tracking, but never found a way to call the mentioned functions nor to set the value that they return.

    I did find how to access the change tracking tables as DAC and wrote a script to compare the tables (attached).

    I found the change tracking tables were being cleaned up, but any record in MyDatabase.sys.dm_tran_commit_table from before the
    i.e. the automatic clean up had not been working since we applied SQL Server 2014 SP3.

    In the article
    https://www.brentozar.com/archive/2014/06/performance-tuning-sql-server-change-tracking/
    You stated “you might at times wonder if you’re crazy, or if Change Tracking is haunted.”

    I was feeling this way. Finally CU4 was released.

    https://repltalk.com/2019/05/21/change_tracking_hardened_cleanup_version-not-incrementing-in-sql-2014-sp3/
    States “Great news! On July 28th 2019 we released SQL 2014 SP3 CU4 with the corrected cleanup code.”

    We applied CU4 to Development.
    I turned auto_cleanup off
    Ran manual cleanup. Still had the problem.
    I turned auto_cleanup on
    An hour later I checked again and MyDatabase.sys.dm_tran_commit_table now matched the retention_period.
    As you stated “you might at times wonder if you’re crazy, or if Change Tracking is haunted.”

    The same thing happened in QA and Prod . . . CU4 seemed to have no affect, but an hour later it was fixed.

    Thank you,
    Bob Sturnfield

  2. –find syscommittab change tracking mismatches.sql By Bob Sturnfield
    Use [MyDatabase]
    set nocount on
    — Loop thru change tracking tables in a database to map to syscommittab — run as DAC Dedicated Administator
    if (object_id(‘tempdb..#CT_Tables’) is not null) drop table #CT_Tables
    — Fetch the tables enabled for Change Tracking
    select identity(int, 1,1) as TableID, ctt.object_id, ctt.cleanup_version, (SCHEMA_NAME(tbl.Schema_ID) +’.’+ object_name(ctt.object_id)) as TableName
    into #CT_Tables
    from sys.change_tracking_tables ctt
    INNER JOIN sys.tables tbl
    ON tbl.object_id = ctt.object_id
    ORDER BY (SCHEMA_NAME(tbl.Schema_ID) +’.’+ object_name(ctt.object_id))

    if (object_id(‘tempdb..#CT_xdes_id’) is null)
    create table #CT_xdes_id(
    [xdes_id] bigint,
    [object_id] int,
    [tablename] nvarchar(260))

    TRUNCATE TABLE #CT_xdes_id

    — Set up the variables
    declare @start int = 1, @end int = (select count(*) from #CT_Tables), @tablename varchar(255), @object_id int, @cleanup_version bigint, @command nvarchar(4000)
    while (@start <= @end)
    begin
    — Fetch the table to be cleaned up
    select @tablename = TableName, @object_id = object_id, @cleanup_version = cleanup_version from #CT_Tables where TableID = @start
    set @command = 'SELECT chg.sys_change_xdes_id [xdes_id], '+convert(nvarchar(255),@object_id)+' [object_id], '''+@tablename+''' [tablename]
    FROM sys.change_tracking_'+convert(nvarchar(255),@object_id)+' [chg]
    LEFT OUTER
    JOIN sys.syscommittab [ssct]
    ON chg.sys_change_xdes_id=ssct.xdes_id
    '

    — print isnull(@command,'null')
    INSERT INTO #CT_xdes_id ([xdes_id], [object_id], [tablename])
    exec (@command)

    — Increment the counter
    set @start = @start + 1
    end
    drop table #CT_Tables

    SELECT convert(date,dateadd(hour,-5,[ssct].[commit_time])) [commit_date], count(*) [count], [chg].[tablename]
    FROM sys.syscommittab [ssct]
    FULL OUTER
    JOIN #CT_xdes_id [chg]
    ON chg.xdes_id=ssct.xdes_id
    GROUP BY convert(date,dateadd(hour,-5,[ssct].[commit_time])), [chg].[tablename]
    ORDER BY convert(date,dateadd(hour,-5,[ssct].[commit_time])), [chg].[tablename]

    drop table #CT_xdes_id

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.