MSSQL on Windows: Monitoring error (SCOM 2019)

Question

I'm facing this issue in our SCOM 2019 Production and Non-prod environment.

We have checked with SQL team they said timeout and permissions are in place.

Please advise how to fix this issue.

|Management Group: "Management group name"Module:
Microsoft.SQLServer.Windows.Module.Monitoring.Monitors.AlwaysOnSystemPolicyMonitoringVersion:
7.0.42.0Error(s) was(were) occurred:Message: ----------
Exception: ----------Exception Type:
Microsoft.SqlServer.Management.Common.ExecutionFailureExceptionMessage: An
exception occurred while executing a Transact-SQL statement or batch.Source:
mscorlibStack Trace: Server stack trace: at
Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader(SqlCommand
command)at
Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataReader(String query,
SqlCommand& command)at
Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql
execSql, String query)at
Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection
query, Object con, StatementBuilder sb, RetriveMode rm)at
Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType,
StringCollection sql, Object connectionInfo, StatementBuilder sb)at
Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult
sqlresult, ResultType resultType)at
Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult
result)at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()at
Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object
ci)at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object
connectionInfo, Request request)at
Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorDataReader(Request
req)at Microsoft.SqlServer.Management.Smo.SqlSmoObject.InitChildLevel(Urn
levelFilter, ScriptingPreferences sp, Boolean forScripting, IEnumerable1 extraFields)at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.InitializeChildCollection(Boolean refresh, ScriptingPreferences sp, String filterQuery, IEnumerable1
extraFields)at
Microsoft.SqlServer.Management.Smo.SmoCollectionBase.GetEnumerator(ScriptingPreferences
sp)at System.Linq.Enumerable.<OfTypeIterator>d__951.MoveNext()at System.Collections.Generic.List1..ctor(IEnumerable1 collection)at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source)at
System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr
md, Object[] args, Object server, Object[]& outArgs)at
System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(IMessage
msg, IMessageSink replySink)Exception rethrown at [0]: at
System.Runtime.Remoting.Proxies.RealProxy.EndInvokeHelper(Message reqMsg,
Boolean bProxyCase)at
System.Runtime.Remoting.Proxies.RemotingProxy.Invoke(Object NotUsed,
MessageData& msgData)at System.Func1.EndInvoke(IAsyncResult result)at Microsoft.SQLServer.Core.Module.AlwaysOnHelper.Helpers.AsyncHelper.<>c__DisplayClass0_01.<ExecuteAsync>b__0()at
System.Threading.Tasks.Task1.InnerInvoke()at System.Threading.Tasks.Task.Execute()--- End of stack trace from previous location where exception was thrown ---at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)at Microsoft.SQLServer.Core.Module.AlwaysOnHelper.Helpers.AsyncHelper.<ExecuteAsync>d__01.MoveNext()---
End of stack trace from previous location where exception was thrown ---at
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()at
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task
task)at
Microsoft.SQLServer.Windows.Module.Monitoring.Monitors.AlwaysOnSystemPolicyMonitoring.<GetPropertyBagAsync>d__5.MoveNext()---
End of stack trace from previous location where exception was thrown ---at
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()at
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task
task)at
Microsoft.SQLServer.Module.Helper.Base.DataItemHelper.<GetPropertyBagDataAsyncStatic>d__51.MoveNext()---------- Inner Exception: ----------Exception Type: System.Data.SqlClient.SqlExceptionMessage: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.Number: -2Source: .Net SqlClient Data ProviderStack Trace: at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)at Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader(SqlCommand command)---------- Inner Exception: ----------Exception Type: System.ComponentModel.Win32ExceptionMessage: The wait operation timed outNative Error Code: 258HRESULT: 0x80004005State:The configuration properties are: ManagementGroupName = TM-ProdPublisher = SQLMonitoringWindowsConnectionString = CHI4WPMCSQL002B.tm.toppanmerrill.comInstanceEdition = Enterprise EditionInstanceName = MSSQLSERVERInstanceVersion = 15.0.4298.1MachineName = CHI4WPMCSQL002B.tm.toppanmerrill.comMonitoringType = LocalNetbiosComputerName = CHI4WPMCSQL002BLogin = SqlExecTimeoutSeconds = 60SqlTimeoutSeconds = 15TimeoutSeconds = 300Password = ********Error(s):---------- Exception: ----------Exception Type: Microsoft.SqlServer.Management.Common.ExecutionFailureExceptionMessage: An exception occurred while executing a Transact-SQL statement or batch.Source: mscorlibStack Trace: Server stack trace: at Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader(SqlCommand command)at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataReader(String query, SqlCommand& command)at Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql execSql, String query)at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm)at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb)at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorDataReader(Request req)at Microsoft.SqlServer.Management.Smo.SqlSmoObject.InitChildLevel(Urn levelFilter, ScriptingPreferences sp, Boolean forScripting, IEnumerable1
extraFields)at
Microsoft.SqlServer.Management.Smo.SmoCollectionBase.InitializeChildCollection(Boolean
refresh, ScriptingPreferences sp, String filterQuery, IEnumerable1 extraFields)at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.GetEnumerator(ScriptingPreferences sp)at System.Linq.Enumerable.<OfTypeIterator>d__951.MoveNext()at
System.Collections.Generic.List1..ctor(IEnumerable1 collection)at
System.Linq.Enumerable.ToListTSourceat
System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr
md, Object[] args, Object server, Object[]& outArgs)at
System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(IMessage
msg, IMessageSink replySink)Exception rethrown at [0]: at
System.Runtime.Remoting.Proxies.RealProxy.EndInvokeHelper(Message reqMsg,
Boolean bProxyCase)at
System.Runtime.Remoting.Proxies.RemotingProxy.Invoke(Object NotUsed,
MessageData& msgData)at System.Func1.EndInvoke(IAsyncResult result)at Microsoft.SQLServer.Core.Module.AlwaysOnHelper.Helpers.AsyncHelper.<>c__DisplayClass0_01.<ExecuteAsync>b__0()at
System.Threading.Tasks.Task1.InnerInvoke()at System.Threading.Tasks.Task.Execute()--- End of stack trace from previous location where exception was thrown ---at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)at Microsoft.SQLServer.Core.Module.AlwaysOnHelper.Helpers.AsyncHelper.<ExecuteAsync>d__01.MoveNext()---
End of stack trace from previous location where exception was thrown ---at
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()at
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task
task)at
Microsoft.SQLServer.Windows.Module.Monitoring.Monitors.AlwaysOnSystemPolicyMonitoring.<GetPropertyBagAsync>d__5.MoveNext()---
End of stack trace from previous location where exception was thrown ---at
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()at
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task
task)at
Microsoft.SQLServer.Module.Helper.Base.DataItemHelper.<GetPropertyBagDataAsyncStatic>d__5`1.MoveNext()----------
Inner Exception: ----------Exception Type:
System.Data.SqlClient.SqlExceptionMessage: Execution Timeout Expired. The
timeout period elapsed prior to completion of the operation or the server is not
responding.Number: -2Source: .Net SqlClient Data ProviderStack
Trace: at
Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction
action, Object execObject, DataSet fillDataSet, Boolean catchException)at
Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader(SqlCommand
command)---------- Inner Exception: ----------Exception Type:
System.ComponentModel.Win32ExceptionMessage: The wait operation timed
outNative Error Code: 258HRESULT: 0x80004005| | -------- | ||

Ragards,

Adam

Answer 1

Hi,

There are a few areas you can investigate to identify and resolve the problem:

1. SCOM Management Packs: Ensure that all required management packs are installed and up to date. Sometimes, missing or outdated management packs can cause issues with monitoring and data collection.
2. SCOM Service Accounts: Verify that the service accounts used by SCOM have the necessary permissions to access resources and data in your environment. This includes permissions on monitored systems and databases.
3. Event Logs: Check the event logs on the SCOM management server, the monitored servers, and the SQL server for any relevant error messages or warnings. These logs can provide valuable insights into what might be causing the issue.
4. Performance Counters: Monitor performance counters on the SCOM management server, SQL server, and any other relevant servers to identify any resource bottlenecks or performance issues.
5. Firewall and Network Connectivity: Ensure that there are no firewall issues or network connectivity problems between the SCOM management server and the SQL server. A network interruption could cause timeouts or communication errors.
6. Database Maintenance: Check the health and performance of the SCOM databases. Regular maintenance, such as index optimization and database backups, is crucial for smooth operations.
7. Restart Services: Sometimes, a simple restart of SCOM services can resolve intermittent issues.
8. Update Rollups and Hotfixes: Make sure you have the latest SCOM update rollups and hotfixes installed. These updates often include bug fixes and performance improvements.
9. SCOM Management Group Health Check: Utilize SCOM's built-in management group health check feature to identify any configuration or performance issues within the management group.