FIX SCCM SQL Backlog Issues using SQL Transmission Queue Stored Procedures

Let’s try to identify and FIX SCCM SQL Backlog Issues using SQL Transmission Queue Stored Procedures. How to Fix SCCM ConfigMgr Inbox Backlog Issues Endpoint Manager.

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 site-to-site replication post.

A number of Stored Procedures can be used to find more details about the backlog along with monitoring of Transmission Queue.

Patch My PC

The first and very useful one is the stored procedure called “spDiagDRS”. Run “EXEC spDiagDRS” to get the below results (shown in the pic). SCCM SQL Backlog Issues.

Latest PostFIX SCCM SQL Replication Issues Using Replication Link Analyzer

Read MoreFIX: SCCM Site Database Has A Backlog Of SQL Change Tracking Data

How to Fix SCCM ConfigMgr Inbox Backlog Issues Endpoint Manager

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

How to Fix SCCM ConfigMgr Inbox Backlog Issues Endpoint Manager
How to Fix SCCM ConfigMgr Inbox Backlog Issues Endpoint Manager

The stored procedure “spDiagDRS” will offer details about queued messages. Have a look at the columns named “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 some error in the send and we have a backlog.

The stored procedure “spDiagDRS” will also tell us about the Status and LastSyncTime of each Replication Group. In my example, SiteSending is CAS and SiteReceiving is PR1. SCCM SQL Backlog Issues

How to Fix SCCM ConfigMgr Inbox Backlog Issues Endpoint Manager
How to Fix SCCM ConfigMgr Inbox Backlog Issues Endpoint Manager

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

How to Fix SCCM ConfigMgr Inbox Backlog Issues Endpoint Manager
How to Fix SCCM ConfigMgr Inbox Backlog Issues Endpoint Manager

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.

How to Fix SCCM ConfigMgr Inbox Backlog Issues Endpoint Manager
How to Fix SCCM ConfigMgr Inbox Backlog Issues Endpoint Manager

More details about these stored procedures in future blog posts. For the time being, you can check out the following examples of these along with parameters.

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

Transmission Queue is another option that we should look at, in case of a backlog (or the outgoing messages are stuck).

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

How to Fix SCCM ConfigMgr Inbox Backlog Issues Endpoint Manager
How to Fix SCCM ConfigMgr Inbox Backlog Issues Endpoint Manager

SCCM SQL Transmission Queue

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

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 below pic, you can see the records waiting for transmission. Have a look at the “transmission_status” column, this will provide more details about any transmission errors. This will be very helpful for further troubleshooting.

How to Fix SCCM ConfigMgr Inbox Backlog Issues Endpoint Manager
SCCM SQL Transmission Queue – How to Fix SCCM ConfigMgr Inbox Backlog Issues Endpoint Manager

How to Fix SCCM ConfigMgr Inbox Backlog Issues Endpoint Manager?

How to Fix SCCM ConfigMgr Inbox Backlog Issues Endpoint Manager
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. SCCM SQL Backlog Issues

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
SCCM SQL Backlog Issues
How to Fix SCCM ConfigMgr Inbox Backlog Issues Endpoint Manager

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 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”.

Also, this process will start tracing each and every event of the SQL server. To stop trace use “EXEC spDiagStopTrace”. Ensure that you STOP the trace ASAP otherwise it may create some adverse impact on the server. SCCM SQL Backlog Issues.

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.

image

Author

Anoop is Microsoft MVP! He is a Device Management Admin with more than 20 years of experience (calculation done in 2021) in IT. He is Blogger, Speaker, and Local User Group HTMD Community leader. His main focus is on Device Management technologies like SCCM 2012, Current Branch, and Intune. He writes about ConfigMgr, Windows 11, Windows 10, Azure AD, Microsoft Intune, Windows 365, AVD, etc.

10 thoughts on “FIX SCCM SQL Backlog Issues using SQL Transmission Queue Stored Procedures”

  1. Hi Anoop, good one. So, as per this I see that I have 13412 in “OutgoingMessagesInQueue”. Now, I certain points on which I need clarification are:-

    1. Is this because of these many messages are there in queue, my database replication status showing as “Link has failed” ? If yes, how can I push them or probably clear them to make the link active?
    2. If not, what could be the reason for my link showing as failed when I have verified that all the necessary ports are open and as a matter of fact, it worked till couple of days back.
    3. I understand that these backlogs will get generated when the link breaks(hope my understanding is correct). If yes, when the network link gets rectified shouldn’t be these backlogs start getting pushed and make the database replication healthy?

    I am not a SCCM guy, so confused within few concepts. I would really appreciate if you can throw some light on it.

    Reply
  2. I have entries in my ConfigMgrRCMQueue related to an old site.
    This caused the ConfigMgrRCMQueue te become disabled and I can’t enable it. Now my other links to other secondaries is down.
    Any idea how to get removed the entries in ConfigMgrRCMQueue so that it can be enabled again ?

    Reply
  3. Hi Anoop,

    I have entries in my ConfigMgrRCMQueue which caused all my links to be down.
    Could you tell me how i can clear the entries from ConfigMgrRCMQueue ?
    Those entries are related to old secondary sites 🙁

    Reply
  4. Hello,

    I am facing the following issue:

    We have 4 Primary sites and one Central CAS Server. We lost the The CAS site by a HD failure. We only have a recent backup of the Site CAS Database only no backup for any configuration for the site.

    We Prepared a new server did a Fresh installation of Windows 2008 R2 Sp1 install all Prerequisite for System Center Configuration Manager 2012 Sp1 and Run the Setup for Sysem Center Configuration Manager. I choose reinstall

    1- Recover A Site
    2- Reinstall this site Server (CAS Server)
    3- Use a site Database that has been manually recovered. (I restored the last backup of the database)

    Follow the Wizard, and the installation completed successfully.

    Now when I am opening my CAS Console it is Read only mode. It has been like this since 24 hrs so far and no sign of improvement.

    If I open any other primary site it is also in Read Only mode. All Site are in the unknow state, see attachements.

    Is this a normal behavior? How Can I stop this replication?

    What should I do next?

    any helps,

    Thanks,

    Reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.