DeltaSynchronization issue with SCOM 2022

Question

We're installing a new SCOM 2022 UR2 environment. This is a fresh installation on fresh Windows 2022 VMs. The SQL Databases are also new, and also 2022 CU10.

After installation we are importing all the Management Packs from our existing 2019 environment.

At some point we see an error in the OpsDB by running this command:

SELECT * FROM cs.workitem
WHERE WorkItemName like '%delta%'
ORDER BY WorkItemRowId DESC

Microsoft.EnterpriseManagement.ManagementConfiguration.DataAccessLayer.DataAccessException: Data access operation failed at Microsoft.EnterpriseManagement.ManagementConfiguration.DataAccessLayer.DataAccessOperation.ExecuteSynchronously(Int32 timeoutSeconds, WaitHandle stopWaitHandle) at Microsoft.EnterpriseManagement.ManagementConfiguration.CmdbOperations.CmdbDataProvider.GetConfigurationDelta(String watermark) at Microsoft.EnterpriseManagement.ManagementConfiguration.Engine.TracingConfigurationDataProvider.GetConfigurationDelta(String watermark) at Microsoft.EnterpriseManagement.ManagementConfiguration.Engine.DeltaSynchronizationWorkItem.TransferData(String watermark) at Microsoft.EnterpriseManagement.ManagementConfiguration.Engine.DeltaSynchronizationWorkItem.ExecuteSharedWorkItem() at Microsoft.EnterpriseManagement.ManagementConfiguration.Interop.SharedWorkItem.ExecuteWorkItem() ----------------------------------- System.Data.SqlClient.SqlException (0x80131904): A severe error occurred on the current command. The results, if any, should be discarded. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows) at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more) at System.Data.SqlClient.SqlDataReader.TryNextResult(Boolean& more) at System.Data.SqlClient.SqlDataReader.NextResult() at Microsoft.EnterpriseManagement.ManagementConfiguration.CmdbOperations.EntityChangeDeltaReadOperation.ReadData(SqlDataReader reader) at Microsoft.EnterpriseManagement.ManagementConfiguration.DataAccessLayer.ReaderSqlCommandOperation.SqlCommandCompleted(IAsyncResult asyncResult) ClientConnectionId:143f95a7-aacd-4b1e-840a-4a80d60a7ffa

This is also logged on the SCOM Management server as event 29181

The 1st time we experienced this, we just started. We couldn't find the issue so we started over. After a lot of work, we got this issue the 2nd time. We didn't want to start over again, so we did a OpsDB restore, and re-added the same Management Packs as we did before the restore, but the issue stayed away.

This went on for a long time, but now we got this issue back for the 3rd time. We have no idea what is causing this. All the Management Packs we added the first time when this occurred, were imported for a long time now without any problems. And beside that, all those Management Packs are running in our 2019 environment for years without any problem.

The only exception we can find in the database using the SQL server profiler is: incorrect syntax near ')' management configuration service

This syntax error started occurring at the same time the DeltaSynchronization issue starts.

When deep diving in this problem we found out that "PROCEDURE [CS].[p_EntityChangeDeltaList]" is causing this syntax error. When removing the selected piece of comment the problem disappears.

Even only removing the spaces last line of this comment, is enough to resolve the issue.

Problem solved we could say... but why is a comment causing an error, and why does this occur at a sudden moment, and not from the beginning? After a DB restore the problem was gone for a while, but the comment in the SQL query has always been there.

Answer 1

Let me first say that I know nothing about Operations Manager. I answer this from the SQL Server side.

I can see two possible reasons why removing parts of the comment helps, but one is more far-fetched than the other, so I only cover one of them.

That is, there was a bug were users would spuriously get this error about "incorrect syntax near ')'", and in some cases it was very spuriously. This bug was fixed in CU11 - one above the level you are on. So download and install CU12 (the most recent CU) for SQL 2022.

But I will have to admit that this is a bit of a shot in the dark.

I note that you also have this exception:

A severe error occurred on the current command. The results, if any, should be discarded

This message often goes together with dumps in the SQL Server errorlog. Have you checked the errorlog?

But this error message also occurs if things go wrong on the SqlClient side, often because SQL Server spits out incorrect TDS. This is nothing that I recognise from the thread we had about incorrect syntax. Then again, this may be due to that the syntax error when running the dynamic SQL leads to further complications.

So, to sum up: Apply CU12. If the issue persists, we need to talk further. (But you should probably open a case with Microsoft.)