Unable to send update on component warnings are repeatedly logged in Smsdbmon.log

This article fixes an issue in which Smsdbmon.log repeatedly reports warnings for multiple triggers, such as Unable to send update on component PolicyTargetEvalNotify_iud.

Original product version:   Configuration Manager (current branch - version 1810)
Original KB number:   4494362

Symptoms

After you update to Configuration Manager current branch version 1810, one or more of the following warning messages are repeatedly logged in Smsdbmon.log:

• 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

Because of the backlog in the TableChangeNotifications table generated by these triggers, you may experience issues such as slow content distribution.

Cause

During the setup of Configuration Manager current branch version 1810, the following triggers are removed:

• 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

In some cases, the SMS_EXCUTIVE (SMSExec.exe) starts during the setup. When this occurs, the SMS DB Monitor (SMSDBMon.exe) re-creates the triggers using the values from the cached registry key located at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SMS\Triggers\<SiteCode>.

To verify that this is the issue, compare the time that the update starts in CMUpdate.log and the time that SMSExec starts in the System event log to determine whether SMSExec starts after the setup runs.

You can also identify when the SMSDBMON component starts and stops by running the following query:

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

Resolution

To fix this issue, update to Configuration Manager current branch version 1902.

Workaround

To work around this issue in Configuration Manager current branch version 1810:

1. Run the registry editor, locate the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SMS\Triggers\<SiteCode of the affected site> registry key, and then determine whether the following registry values exist:

   • PolicyTargetEvalNotify_iud
   • PolicyTargetEvalNotify_ColMember_iu
   • PolicyAssignmentChg_Notify_iu

2. If the values exist, delete them.

3. To drop the triggers from the database, run the following SQL statements on the site database that experiences this issue:

   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
   

4. To clean the TableChangeNotifications table, run the following SQL statements:

   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