This post should help you to fix the warning “The site database has a backlog of SQL change tracking data”. I am going to cover about SQL Server change tracking cleanup introduced in SCCM 1810.
Starting in SCCM 1810, many admins started to see a warning while performing the prerequisite check. The site database has a backlog of SQL change tracking data.
This warning is seen because the in-console update wizard now performs a check to see if the site database has a backlog of SQL change tracking data.
Most of all, this warning doesn’t prevent you from installing the update. It’s a warning not an error. You can ignore the warning and install the update. But why do you want see that Yellow exclamation icon when you can actually fix it.
The below warning appeared while performing SCCM 1910 Prerequisite check.
Microsoft explains why this warning comes up and also provides a fix for this. The solution is already documented here however I believe the steps covered in this post should help you to fix the warning.
The site database has a backlog of SQL change tracking data
You can manually verify if the site database has a backlog of SQL change tracking data. First of all, create a diagnostic connection to your site database. Now here is the common error that you see while performing this step.
Using SQL Server Management Studio’s when you connect to admin:<instance name>, you see an error. Dedicated administrator connections are not supported via SSMS as it establishes multiple connections by design. (Microsoft.SqlServer.Management.SqlStudio.Explorer).
So to create a diagnostic connection to site database, connect to your SCCM Database engine first.
Create a new Database Engine Query and connect to admin:<instance name>.
In a dedicated administrator connection query window, enter the below commands. Replace the ConfigMgr Database name with your site database name. Click Execute.
USE <ConfigMgr database name>
EXEC spDiagChangeTracking
Wait for the query to run and now look at the CT_Days_Old value which is 190 days in my case. This value tells you the age (days) of the oldest entry in your syscommittab table.
As per Microsoft, the value should be five days, which is the Configuration Manager default value. Most of all don’t modify or change this default value at any cost.
At times of heavy data processing or replication, the oldest entry in syscommittab could be over five days. If this value is above seven days, run a manual cleanup of change tracking data.
To clean up the change tracking data, run the below command in the dedicated administration connection.
USE <ConfigMgr database name>
EXEC spDiagChangeTracking @CleanupChangeTracking = 1The above command starts a cleanup of syscommittab and all of the associated side tables. It may take several minutes or several hours to complete.
Finally take a look at CT_Days_Old value which is now 4.
Close the SQL server management studio.
Run Configuration Manager Prerequisite Check
In the SCCM console, go to Updates and Servicing node. Right click the Configuration Manager update and run prerequisite check again.
Congratulations you have successfully fixed “The site database has a backlog of SQL change tracking data” warning. I hope this post helps.
Thanks Prajwal.
Your work on SCCM is super useful.
Thank you Conal.
In our case the CM DB was owned by the user that installed it instead of SA as dbo. Lots of SQL errors because of this.
I performed the necessary steps, however, it appears to be unsuccessful.
What actually causes this issue? I run into it every time we upgrade…
You may ignore this warning as it doesn’t halt the upgrade install.
This is really a helpful post. Finally we can remove that warning.
Good post.. But on my server nothing happens.
So now I try to set the retension period to 1 hour and see what happens. Will set it back to 5 days afterwards.
On my server nothing happens as well. I was also thinking of changing the retention to 1 hour and then back to 5 days. Did it work for you?
Excellent Post. I have been banging my head up against the wall for explanation to why this was occurring.
Thank you!