FIX SCCM SQL Backlog Issues using SQL Transmission Queue Stored Procedure

Let’s try to identify and fix SCCM SQL Backlog Issues using SQL Transmission Queue Stored Procedures. Here’s how to Fix SCCM ConfigMgr Inbox Backlog Issues.

You can have a look at the Site to Site Replication post for more details about the new replication model. This post is a continuation of the site-to-site replication post.

Several Stored Procedures can be used to obtain more details about the backlog and monitor the Transmission Queue.

The first and most useful one is the stored procedure called spDiagDRS. Run EXEC spDiagDRS to get the below results (shown in the picture): SCCM SQL Backlog Issues.

• FIX SCCM SQL Replication Issues Using Replication Link Analyzer
• FIX: SCCM Site Database Has A Backlog Of SQL Change Tracking Data

Index
How to Fix SCCM ConfigMgr Inbox Backlog Issues
SCCM SQL Transmission Queue
SCCM SQL Backlog Issues

How to Fix SCCM ConfigMgr Inbox Backlog Issues

Let’s try to understand how to Fix SCCM ConfigMgr Inbox Backlog Issues. The following are some of the SQL-stored procedures and queries that can help you fix replication issues. However, you should start troubleshooting SCCM replication issues from the Replication Link Analyzer.

The stored procedure spDiagDRS offers details about queued messages. Look at the columns OutgoingMessagesInQueue and IncomingMessagesInQueue.

In an ideal scenario, there should NOT be any queued messages, and the values of those columns should be ZERO. In my example, “OutgoingMessagesInQueue” is 257, which means there is some error in the send, and we have a backlog.

The stored procedure “spDiagDRS” will also tell us about each replication group’s status and last sync time. In my example, SiteSending is CAS, and SiteReceiving is PR1. 

The following spDiagDRS – How to Fix SCCM ConfigMgr Inbox Backlog Issues.

Apart from spDiagDRS, there are some very useful stored procedures that we can use at the time of backlog troubleshooting. See the list of Stored Procedures below.

We will provide more details about these stored procedures in future blog posts. For now, you can check out the following examples and parameters.

EXEC spDiagMessagesInQueue
EXEC spDiagGetReplicationGroupStats
EXEC spDiagGetProcedureStats
EXEC spDiagGetQueryStats
EXEC spDiagGetRunningQueries
EXEC spDiagStartTrace
EXEC spDiagStopTrace

Transmission Queue is another option that we should consider in case of a backlog (or stuck outgoing messages).

All the other queues (ConfigMgrDRSSiteQueue, ConfigMgrRCMQueue, ConfigMgrDRSMsgBuilderQueue, ConfigMgrDRSQueue etc. ) shown in the following pic are application-related.

SCCM SQL Transmission Queue

To check the Transmission Queue, you need to run the SQL query below. With the query below, we can check Transmission for a particular primary site (in the below query, CAS server site code = CAS, and the Primary site code is PR1). 

SQL Query to check the transmission_status in SCCM SQL-based replication is given in the below link – SCCM-SQL-backlog-Check-Query/SQL Backlog Check.sql at main · AnoopCNair/SCCM-SQL-backlog-Check-Query (github.com)

In the picture below, you can see the records waiting for transmission. The “transmission_status” column provides more details about any transmission errors, which will be very helpful for further troubleshooting.

How to Fix SCCM ConfigMgr Inbox Backlog Issues Endpoint Manager?

vLogs view is the DRS (Data Replication Service) log file. This will provide us with more details about the DRS process and backlog. 

Run the following SQL query – to get more details about these logs.

Select top 1000 * from vLogs order by LogTime desc

SCCM SQL Backlog Issues

RCM_ReplicationLinkStatus table can also provide us with more details about the link status between the sites.

Run the SQL Query – “select * from RCM_ReplicationLinkStatus”. Look at the StatusName column for more details like Failed, Degraded, etc.

select * from RCM_ReplicationLinkStatus

TRACE stored procedure – For In-depth analysis of backlog. This can be performed by using the following stored procedures. Caution – this will create a lot of overhead on the SQL server and also use a lot of disk space because of the creation of the ConfigMgrDBTrace.trc file.

EXEC spDiagStartTrace
EXEC spDiagStopTrace

You can start the trace process with EXEC spDiagStartTrace.  This process will create trc (trace file) – in the SQL installed location.

“C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ConfigMgrDBTrace trc”.

This process will also start tracing every SQL server event. To stop the trace, use EXEC spDiagStopTrace. Ensure that you STOP the trace ASAP; otherwise, it may adversely impact the server. 

You can use SQL Server Profiler to open the trc file. You will get depth details about each event performed by the SQL server during the time of TRACE. like Duration, EventClass, StartTime, etc.

We are on WhatsApp. To get the latest step-by-step guides and news updates, Join our Channel. Click here –HTMD WhatsApp.

Author

Anoop C Nair has been Microsoft MVP from 2015 onwards for 10 consecutive years! He is a Workplace Solution Architect with more than 22+ years of experience in Workplace technologies. He is also a Blogger, Speaker, and leader of the Local User Group Community. His main focus is on Device Management technologies like SCCM and Intune. He writes about technologies like Intune, SCCM, Windows, Cloud PC, Windows, Entra, Microsoft Security, Career, etc.