Fix SCCM Site Database has a backlog of SQL Tracking Data

In this post, I will show how to resolve the warning “SCCM site database has a backlog of SQL change tracking data”. You can use the spDiagChangeTracking stored procedure to check SQL Server change tracking data backlog for SCCM DB.

Starting in SCCM version 1810, many ConfigMgr admins saw a new warning during the prerequisite check – The site database has a backlog of SQL change tracking data. You see this warning because the in-console update wizard now performs a check to see if the site database has a backlog of SQL change tracking data.

The Microsoft SCCM team frequently updates the prerequisite checker tool to prevent SCCM hotfix or update installation failures during in-console updates and servicing. Microsoft, for example, added a new prerequisite check “Co-Mgmt slider is not pointed to Intune” with the SCCM 2211 update. These prerequisite warnings, thankfully, can be resolved by making a few changes to your setup.

Fix SCCM Site Database has a backlog of SQL Tracking Data

The screenshot below shows the following prerequisite check warning while installing the SCCM current branch update – The site database has a backlog of SQL change tracking data. You can resolve this warning by using the spDiagChangeTracking stored procedure to check SQL Server change tracking data backlog for SCCM DB.

In SCCM, the prerequisite check warnings are displayed in two locations:

1. ConfigMgrPrereq.log located on the SCCM primary server.
2. On the Update Pack Installation Status in the Monitoring workspace.

For detailed information about SCCM prerequisite check error or warning, it is recommended that you review the ConfigMgrPrereq.log. This log file records the details of the SCCM prerequisite checks and update installation procedure.

To understand more about the SQL change tracking data and resolve the above prerequisite check warning, you can refer to the following Microsoft document. We will now go over the procedure to resolve the warning SCCM site database has a backlog of SQL data.

Step 1: Setup Dedicated Administration Connection (DAC) to SCCM DB

As previously stated, you can check the SQL Server change tracking data backlog for SCCM DB using the spDiagChangeTracking stored procedure. You can use the SCCM site DB stored procedure to see if the site database has a backlog of SQL Server change tracking data.

To run the spDiagChangeTracking stored procedure against the SCCM DB, you will need to setup a dedicated administration connection (DAC) to connect to the Configuration Manager database. If you attempt to run the spDiagChangeTracking stored procedure without DAC, you will see the message “DAC Connection required to run this stored procedure.”

When you attempt to connect to SCCM DB admin:<instance name>, you may encounter the following error with SQL Server.

Dedicated administrator connections are not supported via SSMS as it establishes multiple connections by design. (Microsoft.SqlServer.Management.SqlStudio.Explorer).

To resolve the above error, here is the correct procedure to create a diagnostic connection to SCCM site database. Launch the SQL Server Management studio and connect to your SCCM Database engine first by using the below information.

• Server Type: Database Engine
• Server Name: ConfigMgr Server name
• Authentication: Windows Authentication

Step 2: Create a new Database Engine Query

Once you are connected to the database engine, create a new Database Engine Query and connect to admin:<instance name>. The icon to connect create a new database engine query is located on the top bar, and you find it next to the New Query option.

For example, if your Configuration Manager server name is CORPSCCM, use the following server name: ADMIN:CORPSCCM and click on Connect.

Step 3: Check SQL Server Change Tracking Data Backlog for SCCM DB

In this step, we will check the SQL Server change tracking data backlog for our SCCM DB. Once you have established a Dedicated Administration Connection (DAC) to SCCM DB, enter the below commands in the query window. Replace the ConfigMgr Database name with your site database name and click Execute.

USE <ConfigMgr database name>
EXEC spDiagChangeTracking

Wait for the query to run and in the query output, look at the value of CT_Days_Old. In my case, the CT_Days_Old value is 190 days. 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. This is the reason why the prereqwuisite check flagged a warning indicating that SCCM site database has a backlog of SQL tracking data.

Not that you shouldn’t modify or change this default value in the database. 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.

Step 4: Clean up the SQL Change Tracking Data Backlog

To clean up the SQL change tracking data backlog, run the below command in the dedicated administration connection query window.

USE <ConfigMgr database name>
EXEC spDiagChangeTracking @CleanupChangeTracking = 1

The above command starts a cleanup of syscommittab and all the associated side tables. It may take several minutes or several hours to complete the operation.

Once you have performed a cleanup of the SQL change tracking data backlog, run the EXEC spDiagChangeTracking command to check the CT_Days_Old value. This time, the value of CT_Days_Old shows as 4. Indeed, we have now successfully cleaned up the SQL change tracking data backlog on our SCCM DB. Close the SQL server management studio.

Step 5: 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. The SCCM prerequisite checker tool doesn’t report any warnings this time. Congratulations, you have successfully fixed “SCCM site database has a backlog of SQL change tracking data” warning.