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.
|
1 2 |
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
|
1 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
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.
|
1 2 |
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
|
1 2 |
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! 🙂
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
–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], '''[email protected]+''' [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
SCCM admin here.
So far this is the best article on this topic on the Internet. Much appreciated!
Didnt look like my value for CT_Days_Old had changed after running the stored proc with with the cleanup params but when i went back and ran the change tracking sp to check it it had changed to 1.