SQLWatchImporter Timeout Issues

[alex-g-bsc]

Hello everybody,
i need some help with the SQLWatch Importer. I have currently installed SQLWatch in a large Environment with approx. 30 Instances and I am using a Remote Repository. The SQLWatchImporter Job first started to run successfully but i had some issues with Timeouts for some Instances. After it ran over the past 20 Days, I have more and more Instances which ran into Timeouts.
I tried to increase the BulkCopyTimeout but it did not help. When i run those Instances by one, they run successfully at the second time.

Do you have some advice for me?

2022-04-19` 08:02:57.378 ERROR System.AggregateException: One or more errors occurred. ---> System.AggregateException: One or more errors occurred. ---> System.Data.SqlClient.SqlException: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: The wait operation timed out --- End of inner exception stack trace --- at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.SqlCommand.InternalEndExecuteReader(IAsyncResult asyncResult, String endMethod, Boolean isInternal) at System.Data.SqlClient.SqlCommand.EndExecuteReaderInternal(IAsyncResult asyncResult) at System.Data.SqlClient.SqlCommand.EndExecuteReaderAsync(IAsyncResult asyncResult) at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization) --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task) at SqlWatchImport.SqlWatchInstance.<ImportTableAsync>d__48.MoveNext() in C:\projects\sqlwatch\SqlWatch.Monitor\Project.SqlWatchImport\SqlWatchInstance.cs:line 436 --- End of inner exception stack trace --- at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions) at System.Threading.Tasks.Task`1.GetResultCore(Boolean waitCompletionNotification) at System.Threading.Tasks.Task`1.get_Result() at SqlWatchImport.SqlWatchInstance.<>c.<ImportAsync>b__47_3(Task`1 t) in C:\projects\sqlwatch\SqlWatch.Monitor\Project.SqlWatchImport\SqlWatchInstance.cs:line 317 at System.Linq.Enumerable.WhereSelectListIterator`2.MoveNext() at System.Linq.Buffer`1..ctor(IEnumerable`1 source) at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source) at SqlWatchImport.SqlWatchInstance.<>c__DisplayClass47_0.<ImportAsync>b__0() in C:\projects\sqlwatch\SqlWatch.Monitor\Project.SqlWatchImport\SqlWatchInstance.cs:line 317 at System.Threading.Tasks.Task.InnerInvoke() at System.Threading.Tasks.Task.Execute() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at SqlWatchImport.SqlWatchInstance.<ImportAsync>d__47.MoveNext() in C:\projects\sqlwatch\SqlWatch.Monitor\Project.SqlWatchImport\SqlWatchInstance.cs:line 292 --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at SqlWatchImport.Program.<>c__DisplayClass0_0.<<Main>b__1>d.MoveNext() in C:\projects\sqlwatch\SqlWatch.Monitor\Project.SqlWatchImport\Program.cs:line 144 --- End of inner exception stack trace --- at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions) at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken) at System.Threading.Tasks.Task.Wait() at SqlWatchImport.Program.Main(String[] args) in C:\projects\sqlwatch\SqlWatch.Monitor\Project.SqlWatchImport\Program.cs:line 159 ---> (Inner Exception #0) System.AggregateException: One or more errors occurred. ---> System.Data.SqlClient.SqlException: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: The wait operation timed out --- End of inner exception stack trace --- at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.SqlCommand.InternalEndExecuteReader(IAsyncResult asyncResult, String endMethod, Boolean isInternal) at System.Data.SqlClient.SqlCommand.EndExecuteReaderInternal(IAsyncResult asyncResult) at System.Data.SqlClient.SqlCommand.EndExecuteReaderAsync(IAsyncResult asyncResult) at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization) --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task) at SqlWatchImport.SqlWatchInstance.<ImportTableAsync>d__48.MoveNext() in C:\projects\sqlwatch\SqlWatch.Monitor\Project.SqlWatchImport\SqlWatchInstance.cs:line 436 --- End of inner exception stack trace --- at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions) at System.Threading.Tasks.Task`1.GetResultCore(Boolean waitCompletionNotification) at System.Threading.Tasks.Task`1.get_Result() at SqlWatchImport.SqlWatchInstance.<>c.<ImportAsync>b__47_3(Task`1 t) in C:\projects\sqlwatch\SqlWatch.Monitor\Project.SqlWatchImport\SqlWatchInstance.cs:line 317 at System.Linq.Enumerable.WhereSelectListIterator`2.MoveNext() at System.Linq.Buffer`1..ctor(IEnumerable`1 source) at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source) at SqlWatchImport.SqlWatchInstance.<>c__DisplayClass47_0.<ImportAsync>b__0() in C:\projects\sqlwatch\SqlWatch.Monitor\Project.SqlWatchImport\SqlWatchInstance.cs:line 317 at System.Threading.Tasks.Task.InnerInvoke() at System.Threading.Tasks.Task.Execute() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at SqlWatchImport.SqlWatchInstance.<ImportAsync>d__47.MoveNext() in C:\projects\sqlwatch\SqlWatch.Monitor\Project.SqlWatchImport\SqlWatchInstance.cs:line 292 --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at SqlWatchImport.Program.<>c__DisplayClass0_0.<<Main>b__1>d.MoveNext() in C:\projects\sqlwatch\SqlWatch.Monitor\Project.SqlWatchImport\Program.cs:line 144 ---> (Inner Exception #0) System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.SqlCommand.InternalEndExecuteReader(IAsyncResult asyncResult, String endMethod, Boolean isInternal) at System.Data.SqlClient.SqlCommand.EndExecuteReaderInternal(IAsyncResult asyncResult) at System.Data.SqlClient.SqlCommand.EndExecuteReaderAsync(IAsyncResult asyncResult) at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization) --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task) at SqlWatchImport.SqlWatchInstance.<ImportTableAsync>d__48.MoveNext() in C:\projects\sqlwatch\SqlWatch.Monitor\Project.SqlWatchImport\SqlWatchInstance.cs:line 436 ClientConnectionId:3c3d43e9-2023-4de7-8c39-01e906201c34 Error Number:-2,State:0,Class:11<--- <--- (Thread: 1)

[marcingminski]

I am going to guess that the timeout is caused by the central repository as it's not able to handle the workload.
You either need to get a beefier central repo, or try version 5.0. Version 5 utilises queues to smooth out the workload and I have been able to have it pull data from over 100 instances continuously.
However, for various reasons, version 5.0 isn't yet officially released so you may have to get your hands dirty with some coding.