Home configuration manager smsdbmon sql server troubleshooting

Troubleshooting Configuration Manager: Resolving Recurring Warnings in Smsdbmon.log

Updated: • 0 • min read
Table of Contents

Troubleshooting Configuration Manager: Resolving Recurring Warnings in Smsdbmon.log

Configuration Manager (ConfigMgr), formerly known as System Center Configuration Manager (SCCM), is a powerful endpoint management solution used by organizations to manage large groups of computers running Windows, macOS, Linux, and mobile devices. A critical component of ConfigMgr is its site database, which stores all configuration data and is constantly monitored for changes that require action. The SMS Database Notification Monitor (SMSDBMON) is responsible for watching the ConfigMgr site database for these changes and notifying other ConfigMgr components to process them. These changes are often related to policy updates, collection memberships, and other configuration shifts. When SMSDBMON detects relevant changes, it triggers actions within the ConfigMgr hierarchy.

Proper functioning of SMSDBMON and the database triggers it relies upon is essential for the overall health and responsiveness of a ConfigMgr environment. Issues with this component can lead to significant delays in policy application, software distribution, and site operations. This article addresses a specific issue encountered after updating to Configuration Manager current branch version 1810, where SMSDBMON repeatedly logs warnings related to certain database triggers, leading to performance degradation.

Symptoms

Following an update to Configuration Manager current branch version 1810, administrators may observe a specific pattern of warning messages appearing frequently in the Smsdbmon.log file located on the site server. These messages indicate a problem with SMSDBMON’s ability to process notifications related to policy targeting and assignment changes. The recurring warnings often include one or more of the following entries:

  • WARNING: Unable to send update on component PolicyTargetEvalNotify_iud
  • WARNING: Unable to send update on component PolicyTargetEvalNotify_ColMember_iu
  • WARNING: Unable to send update on component PolicyAssignmentChg_Notify_iu
  • WARNING: Unable to send update on component PolicyTargetEvalNotify_iud_upd

These persistent warnings are not merely informational; they signify a failure in the change notification mechanism. This failure results in pending notifications accumulating within the TableChangeNotifications table in the ConfigMgr site database. A large backlog in this table can severely impact database performance and consequently, the performance of various ConfigMgr functions. Common issues experienced due to this backlog include, but are not limited to, significantly slow content distribution to distribution points and clients, delayed collection updates, and sluggish policy processing on managed devices. The volume of warnings and the resulting backlog can grow quite large over time, exacerbating the performance problems.

Cause

The root cause of these recurring warnings and the subsequent database backlog in Configuration Manager current branch version 1810 is related to the handling of specific database triggers during the update process. Version 1810 was designed to remove certain older triggers related to policy evaluation and assignment notifications as part of its upgrade procedure. The triggers intended for removal were:

  • SMSDBMON_Collections_L_PolicyTargetEvalNotify_iud_ins
  • SMSDBMON_Collection_MemberChg_Notif_PolicyTargetEvalNotify_ColMember_iu_ins
  • SMSDBMON_PolicyAssignmentChg_Notify_PolicyAssignmentChg_Notify_iu_ins
  • SMSDBMON_Collections_L_PolicyTargetEvalNotify_iud_upd

However, a timing issue can occur during the version 1810 setup process. In some scenarios, the main Configuration Manager service, SMS_EXECUTIVE (SMSExec.exe), might start prematurely while the setup is still underway or immediately after it finishes but before all post-update cleanup is completed. When SMSExec starts, it initializes its various components, including SMSDBMON (SMSDBMon.exe), which is responsible for monitoring database changes using triggers.

SMSDBMON maintains a cached list of database triggers it expects to monitor. This cache is stored in the Windows registry under the key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SMS\Triggers\<SiteCode>, where <SiteCode> is the three-letter code for your ConfigMgr site. If SMSDBMON starts while the registry cache still contains entries for the triggers that were supposed to be removed by the 1810 update setup, it will attempt to re-create these triggers in the database based on the outdated cached information.

Essentially, the update process tries to remove the old triggers, but if SMSExec starts too soon, SMSDBMON uses its old cached configuration to bring them back into existence. These re-created triggers are problematic, leading SMSDBMON to log the “Unable to send update” warnings because the system is not expecting these specific notifications from these specific (and technically superseded) triggers anymore. This creates a cycle where the triggers fire, generate notifications, SMSDBMON fails to process them correctly, logs a warning, and the notification gets stuck in the TableChangeNotifications table.

To confirm this specific cause, administrators can investigate the timing of events during the version 1810 update. By comparing the timestamps in the CMUpdate.log file, which records the progress of the update setup, with the timestamps in the System event log that indicate when the SMS_EXECUTIVE service started, you can determine if SMSExec initiated before the update fully completed its trigger cleanup phase. Additionally, the ConfigMgr site database’s StatusMessages table can provide insight into the SMSDBMON component’s activity.

Consider the following SQL query to check the start and stop times of the SMS_DATABASE_NOTIFICATION_MONITOR component:

select * from StatusMessages where Component = 'SMS_DATABASE_NOTIFICATION_MONITOR' and MessageID in (500, 501, 502, 503, 504) order by Time DESC

This query looks for specific status messages related to SMSDBMON initialization and termination. Analyzing the timestamps of these messages relative to the update completion time recorded in CMUpdate.log can help confirm if SMSDBMON started during or immediately after the problematic update window.

Resolution

The definitive solution to this issue is to update your Configuration Manager current branch environment to version 1902 or later. Subsequent versions of Configuration Manager include fixes that address the timing issue during setup, ensuring that the old triggers are correctly removed and not inadvertently re-created by SMSDBMON.

Upgrading to a later version is the recommended path as it resolves the underlying cause within the update mechanism itself and often includes other bug fixes and improvements. Before performing any major version update, it is crucial to follow standard ConfigMgr update procedures, including reviewing prerequisite checks, backing up the site database, and updating in a lab environment if possible. The update process to 1902 (or later) should properly handle the trigger cleanup, preventing the issue from recurring.

Workaround for Version 1810

If you are currently running Configuration Manager current branch version 1810 and are experiencing this issue but cannot immediately upgrade to version 1902 or later, a manual workaround can be implemented to alleviate the symptoms and prevent further recurrence in the current version. This workaround involves manually removing the problematic registry entries and database triggers, and then cleaning up the existing backlog in the TableChangeNotifications table.

Here are the steps to apply the workaround:

  1. Identify and Remove Problematic Registry Entries:
    The first step is to remove the cached trigger definitions that SMSDBMON might be using to re-create the unwanted triggers.

    • Open the Registry Editor (regedit.exe) on the affected ConfigMgr site server.
    • Navigate to the registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SMS\Triggers\<SiteCode>, replacing <SiteCode> with your specific Configuration Manager site code.
    • Examine the values within this key. Look for the following specific registry values:
      • PolicyTargetEvalNotify_iud
      • PolicyTargetEvalNotify_ColMember_iu
      • PolicyAssignmentChg_Notify_iu
    • If any of these values exist, delete them. Right-click on each value and select “Delete.”
    • Close the Registry Editor.

    Deleting these registry entries ensures that even if SMSDBMON restarts, it will not attempt to re-create the problematic triggers from its cached configuration.

  2. Drop Problematic Database Triggers:
    Although the registry entries are gone, the faulty triggers might still exist in the site database if they were re-created after the 1810 update. These triggers need to be manually removed from the database.

    • Connect to the SQL Server hosting the ConfigMgr site database using SQL Server Management Studio or a similar tool.
    • Open a new query window connected to the site database.
    • Run the following SQL script. This script checks for the existence of the four problematic triggers and drops them if found.

    IF OBJECT_ID('tempdb..#temp') IS NOT NULL
     DROP TABLE #temp

 select name
 INTO #temp
 from sys.objects where type = 'TR' and name in
 ('SMSDBMON_Collections_L_PolicyTargetEvalNotify_iud_ins', 'SMSDBMON_Collection_MemberChg_Notif_PolicyTargetEvalNotify_ColMember_iu_ins', 'SMSDBMON_PolicyAssignmentChg_Notify_PolicyAssignmentChg_Notify_iu_ins','SMSDBMON_Collections_L_PolicyTargetEvalNotify_iud_upd')

 IF Exists (select 1 from #temp)
 BEGIN
     declare @name NVARCHAR(255)
     declare @sql NVARCHAR(255)
     DECLARE DELETE_OLD_TRIGGERS CURSOR FOR
         SELECT A.Name FROM #temp AS A
     OPEN DELETE_OLD_TRIGGERS;
     FETCH NEXT FROM DELETE_OLD_TRIGGERS INTO @name;
     WHILE @@FETCH_STATUS = 0
        BEGIN
             SET @sql = 'DROP TRIGGER ' + @name;
             EXECUTE(@SQL);
          FETCH NEXT FROM DELETE_OLD_TRIGGERS INTO @name;
        END;
     CLOSE DELETE_OLD_TRIGGERS;
     DEALLOCATE DELETE_OLD_TRIGGERS;
 END

    * Execute the script. It will report messages indicating which triggers were dropped, if any were found.

    This step directly removes the source of the unwanted notifications from the database, preventing new warnings from being logged and new entries from accumulating in the TableChangeNotifications table due to these specific triggers.

  3. Clean the TableChangeNotifications Backlog:
    Even after removing the triggers, the TableChangeNotifications table may still contain a significant backlog of entries generated by the faulty triggers before they were dropped. This backlog needs to be cleared to restore database performance.

    • In the same SQL Server Management Studio query window connected to the site database, run the following SQL script. This script deletes entries from TableChangeNotifications that were generated by the problematic components, doing so in batches to minimize database locking and resource contention.

    WHILE 1=1
   BEGIN
     DELETE TOP(1000) FROM TableChangeNotifications
     WHERE ((Component = 'PolicyTargetEvalNotify_iud' AND TableName = 'Collections_L') OR
            (Component = 'PolicyTargetEvalNotify_ColMember_iu' AND TableName = 'Collection_MemberChg_Notif') OR
            (Component = 'PolicyAssignmentChg_Notify_iu' AND TableName = 'PolicyAssignmentChg_Notify') OR
            (Component = 'PolicyTargetEvalNotify_iud_upd'  AND TableName = 'Collections_L'))

IF @@ROWCOUNT <= 0
       BREAK;
 END

    * Execute the script. Allow it to run until the WHILE loop completes (i.e., until a DELETE TOP(1000) operation affects 0 rows, triggering the BREAK). This may take some time depending on the size of the backlog.

    This step cleans up the queue of unprocessed notifications related to the faulty triggers. Once the backlog is cleared and the triggers are removed, the SMSDBMON warnings for these components should cease, and database performance should improve.

After implementing the workaround, monitor the Smsdbmon.log file to confirm that the specific warnings are no longer appearing. Also, observe the performance of content distribution and other ConfigMgr operations to ensure the backlog has been successfully cleared and the issue is mitigated. While this workaround resolves the symptoms in version 1810, upgrading to a later version remains the recommended long-term solution to prevent encountering similar issues during future updates.

Understanding the Impact of TableChangeNotifications Backlog

The TableChangeNotifications table serves as a crucial queue for SMSDBMON. When a change occurs in the ConfigMgr database that requires action from another component (e.g., a collection membership update needs policy evaluation), a notification is inserted into this table. SMSDBMON reads these notifications and informs the relevant component (like PolicyProvider or Collection Evaluator) that work is needed. If this process breaks down, as in the case with the faulty triggers, notifications related to those triggers build up.

A growing TableChangeNotifications table can negatively impact database performance in several ways:
* Increased disk I/O: Reading and writing to a large table takes more time.
* Locking and blocking: Queries against a very large table, especially if deletion operations are running without proper batching (though our workaround script uses batching), can cause locks that block other legitimate database operations.
* Delayed processing: Other, legitimate notifications in the table might be delayed because SMSDBMON is struggling to process or even read through the large volume of backlog entries.

Clearing this table is therefore critical for restoring the database’s responsiveness and the overall efficiency of the ConfigMgr site.

Visualizing the Problem

Consider the following simplified diagram illustrating the flow in the problematic 1810 update scenario:

```mermaid
graph TD
A[ConfigMgr 1810 Update Starts] → B{Update Removes Triggers?};
B – Yes → C[Ideal State: Triggers Removed];
B – No / Failed Due to Timing → D[SMSExec Starts Prematurely];
D → E[SMSDBMON Initializes];
E → F{SMSDBMON Reads Cached Triggers in Registry?};
F – Yes, Outdated Cache → G[SMSDBMON Re-creates Problematic Triggers in DB];
G → H[Database Changes Occur];
H → I[Problematic Triggers Fire];
I → J[Notifications Inserted into TableChangeNotifications];
J → K{SMSDBMON Processes Notifications?};
K – Fails for Problematic Notifications → L[WARNING in Smsdbmon.log];
L → J; % Cycle: Warnings logged, backlog grows
K – Processes Other Notifications → M[Other ConfigMgr Components Notified];

style C fill:#bbf,stroke:#f66,stroke-width:2px;
style L fill:#f9f,stroke:#333,stroke-width:2px;
style J fill:#ccf,stroke:#333,stroke-width:2px;
style D fill:#ffb,stroke:#333,stroke-width:2px;
style G fill:#ffb,stroke:#333,stroke-width:2px;
style B fill:#ccb,stroke:#333,stroke-width:2px;
style F fill:#ccb,stroke:#333,stroke-width:2px;
style K fill:#ccb,stroke:#333,stroke-width:2px;

```

This diagram shows how the premature start of SMSExec combined with an outdated registry cache leads to the re-creation of the problematic triggers, subsequently causing the warning messages and the growing backlog in TableChangeNotifications.

Table of Problematic Triggers and Components

Here is a summary of the specific triggers and the components/tables they relate to, as identified in the warnings:

Warning Component Trigger Name (Intended for Removal) Related Table(s) Primary Function Affected
PolicyTargetEvalNotify_iud SMSDBMON_Collections_L_PolicyTargetEvalNotify_iud_ins Collections_L Policy Targeting/Evaluation (Insert)
PolicyTargetEvalNotify_ColMember_iu SMSDBMON_Collection_MemberChg_Notif_PolicyTargetEvalNotify_ColMember_iu_ins Collection_MemberChg_Notif Collection Membership/Policy (Update)
PolicyAssignmentChg_Notify_iu SMSDBMON_PolicyAssignmentChg_Notify_PolicyAssignmentChg_Notify_iu_ins PolicyAssignmentChg_Notify Policy Assignment Changes (Update)
PolicyTargetEvalNotify_iud_upd SMSDBMON_Collections_L_PolicyTargetEvalNotify_iud_upd Collections_L Policy Targeting/Evaluation (Update)

Understanding which triggers are causing the issue helps in both diagnosing the problem and applying the correct workaround steps, ensuring that the right registry values and database triggers are targeted for removal.

Conclusion

The issue of recurring SMSDBMON warnings related to policy and collection triggers after updating to Configuration Manager current branch version 1810 stems from a timing conflict during the update process that allows outdated triggers to be re-created. This leads to a backlog in the TableChangeNotifications table, resulting in noticeable performance degradation, particularly affecting content distribution and policy processing. While the permanent fix is to upgrade to Configuration Manager version 1902 or later, administrators stuck on 1810 can utilize a manual workaround involving deleting specific registry keys, dropping the problematic database triggers via SQL, and cleaning the TableChangeNotifications backlog. Implementing these steps should resolve the warnings and restore normal site performance.

Have you encountered this specific issue in your Configuration Manager environment? How did you troubleshoot and resolve it? Share your experiences and insights in the comments below. Your contributions can help others facing similar challenges!

Post a Comment