Upgraded a customer to 1810 last week and all of suddenly policy was taking a long time to process. What was going on?

The smsdbmon.log was flooded with the following:

WARNING: Unable to send update on component PolicyTargetEvalNotify_iud 

I contacted the Microsoft product group to see if this is something they had seen with other customers and indeed they had, for a few customers only. They were extremely helpful in assisting with getting this resolved and here is the lowdown.

There were some dbmon SQL triggers  removed from 1810 along with some dbmon trigger definitions in the registry of the primary site server, located under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SMS\Triggers. The triggers were initially deleted, but a couple of minutes later were recreated causing the problem.

This issue is going to be catered for where the upgrade process will clean the effected db tables for these triggers as part of the upgrade to 1902. The root cause is still being investigated but, due to the smsdbmon.log filling up rapidly and the backup log overwriting quickly, this is proving tricky.

This is where you can help, if you upgrade check your smsdbmon.log file immediately after upgrade, if you spot the problem copy off both smsdbmon logs straight away, along with the CMUpdate logs and log a call with CSS. The root cause of 1810 upgrades can then be established and resolve for this release.

If you are suffering from the issue here’s what you can do to resolve:

Run the below queries first and save their output for CSS investigations apart from the logs asked above.

select * from DBSchema where objectname in (‘SMSDBMON_Collections_L_PolicyTargetEvalNotify_iud_ins’, ‘SMSDBMON_Collection_MemberChg_Notif_PolicyTargetEvalNotify_ColMember_iu_ins’, ‘SMSDBMON_PolicyAssignmentChg_Notify_PolicyAssignmentChg_Notify_iu_ins’)

select * from DBSchemaChangeHistory where objectname in (‘SMSDBMON_Collections_L_PolicyTargetEvalNotify_iud_ins’, ‘SMSDBMON_Collection_MemberChg_Notif_PolicyTargetEvalNotify_ColMember_iu_ins’, ‘SMSDBMON_PolicyAssignmentChg_Notify_PolicyAssignmentChg_Notify_iu_ins’)

Now coming to the resolution. The process is to select from the TableChangeNotifications table, you’ll see how many rows have been created, drop the trigger and then delete from the TableChangeNotifications table. After the deletion of the rows, you can run the select command to confirm no further rows are being generated.

The commands to run are:

select * from tablechangenotifications where component = ‘PolicyTargetEvalNotify_iud’ and TableName = ‘Collections_L’

DROP TRIGGER [dbo].[SMSDBMON_Collections_L_PolicyTargetEvalNotify_iud_ins]

delete from tablechangenotifications where component = ‘PolicyTargetEvalNotify_iud’ and TableName = ‘Collections_L’

 

select * from tablechangenotifications where component = ‘PolicyTargetEvalNotify_ColMember_iu’ and TableName = ‘Collection_MemberChg_Notif’

DROP TRIGGER [dbo].[SMSDBMON_Collection_MemberChg_Notif_PolicyTargetEvalNotify_ColMember_iu_ins]

delete from tablechangenotifications where component = ‘PolicyTargetEvalNotify_ColMember_iu’ and TableName = ‘Collection_MemberChg_Notif’

 

select * from tablechangenotifications where component = ‘PolicyAssignmentChg_Notify_iu’ and TableName = ‘PolicyAssignmentChg_Notify’

DROP TRIGGER [dbo].[SMSDBMON_PolicyAssignmentChg_Notify_PolicyAssignmentChg_Notify_iu_ins]

delete from tablechangenotifications where component = ‘PolicyAssignmentChg_Notify_iu’ and TableName = ‘PolicyAssignmentChg_Notify’

 

select * from tablechangenotifications where component = ‘PolicyTargetEvalNotify_iud_upd’ and TableName = ‘Collections_L’

DROP TRIGGER [dbo].[SMSDBMON_Collections_L_PolicyTargetEvalNotify_iud_upd]

delete from tablechangenotifications where component = ‘PolicyTargetEvalNotify_iud_upd’ and TableName = ‘Collections_L’

Thanks ever so much to Umair Khan for his assistance and patience with this problem. You can check out Umair’s blog here https://blogs.technet.microsoft.com/umairkhan/

*** Update ***

Umair sent over a couple of tweets which explained the root cause of the problem:

*** Update 2 ***
Here’s the official KB from MS on the problem
https://support.microsoft.com/en-us/help/4494362/unable-to-send-update-on-component-policytargetevalnotify-iud-repeated