Reporting database stopped collecting data after certain date

Question

We are working on Sitecore 9.3.

I see that the data in reporting database tables are present only till date - 27 May 2022. After that, no data has been collected. In the experience profile, latest visits are getting updated but Experience Analytics is also not showing latest interactions.

Can anybody please suggest how to troubleshoot this issue?

Update 1 - Added screenshot of analytics dashboard

Update 2 -

After careful inspection of logs of last 3 months (time duration during which interactions are not showing), I found below 3 kinds of errors in logs -

1.

     3940 03:07:55 ERROR [Path Analyzer] Interaction Interaction {5f4a2423-cdd3-0800-0000-066df7680cf5} could not be collected due to error
        Exception: System.AggregateException
        Message: One or more errors occurred.
        Source: mscorlib   at System.Threading.Tasks.Task`1.GetResultCore(Boolean waitCompletionNotification)   at Sitecore.PathAnalyzer.Processing.TreeAggregator.GetContact(IEntityReference`1 contactReference)   at Sitecore.PathAnalyzer.Processing.TreeAggregator.GetFullInteraction(Interaction interaction)   at Sitecore.PathAnalyzer.Processing.TreeAggregator.Aggregate(ItemBatch`1 batch)
        
        Nested Exception
        
        Exception: Sitecore.XConnect.XdbCollectionUnavailableException
        Message: An error occurred while sending the request.
        Source: Sitecore.XConnect.Client   at Sitecore.XConnect.Client.WebApi.CollectionBatchWebApiClient.<ExecuteBatch>d__11.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 Sitecore.XConnect.Client.Operations.HttpOperationInvoker.<Execute>d__8.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 Sitecore.XConnect.XdbContext.<ExecuteBatchAsyncInternal>d__101.MoveNext()
        --- End of stack trace from previous location where exception was thrown ---   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()   at Sitecore.XConnect.XdbContext.<ExecuteBatchAsyncInternal>d__101.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 Sitecore.XConnect.XdbContext.<ExecuteAsync>d__68.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 Sitecore.XConnect.XdbContext.<GetAsync>d__62`1.MoveNext()
        
        Nested Exception
        
        Exception: System.OutOfMemoryException
        Message: Exception of type 'System.OutOfMemoryException' was thrown.
        Source: mscorlib   at System.IO.MemoryStream..ctor(Int32 capacity)   at System.Net.Http.HttpContent.LimitMemoryStream..ctor(Int32 maxSize, Int32 capacity)   at System.Net.Http.HttpContent.CreateMemoryStream(Int64 maxBufferSize, Exception& error)   at System.Net.Http.HttpContent.LoadIntoBufferAsync(Int64 maxBufferSize)   at System.Net.Http.HttpClient.StartContentBuffering(HttpRequestMessage request, CancellationTokenSource cancellationTokenSource, TaskCompletionSource`1 tcs, HttpResponseMessage response)   at System.Net.Http.HttpClient.<>c__DisplayClass55_0.<SendAsync>b__0(Task`1 task)
        --- 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 Sitecore.Xdb.Common.Web.CommonWebApiClient`1.<ExecuteAsync>d__41.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 Sitecore.XConnect.Client.WebApi.CollectionBatchWebApiClient.<ExecuteBatch>d__11.MoveNext()

        6444 04:22:14 ERROR [Path Analyzer] Interaction Interaction {5f4a2423-cdd3-0800-0000-066dfa764874} could not be collected due to error
        Exception: System.AggregateException
        Message: One or more errors occurred.
        Source: mscorlib   at System.Threading.Tasks.Task`1.GetResultCore(Boolean waitCompletionNotification)   at Sitecore.PathAnalyzer.Processing.TreeAggregator.GetContact(IEntityReference`1 contactReference)   at Sitecore.PathAnalyzer.Processing.TreeAggregator.GetFullInteraction(Interaction interaction)   at Sitecore.PathAnalyzer.Processing.TreeAggregator.Aggregate(ItemBatch`1 batch)
        
        Nested Exception
        
        Exception: Sitecore.XConnect.XdbCollectionUnavailableException
        Message: An error occurred while sending the request.
        Source: Sitecore.XConnect.Client   at Sitecore.XConnect.Client.WebApi.CollectionBatchWebApiClient.<ExecuteBatch>d__11.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 Sitecore.XConnect.Client.Operations.HttpOperationInvoker.<Execute>d__8.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 Sitecore.XConnect.XdbContext.<ExecuteBatchAsyncInternal>d__101.MoveNext()
        --- End of stack trace from previous location where exception was thrown ---   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()   at Sitecore.XConnect.XdbContext.<ExecuteBatchAsyncInternal>d__101.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 Sitecore.XConnect.XdbContext.<ExecuteAsync>d__68.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 Sitecore.XConnect.XdbContext.<GetAsync>d__62`1.MoveNext()
        
        Nested Exception
        
        Exception: Sitecore.Xdb.Common.Web.ConnectionTimeoutException
        Message: A task was canceled.
        Source: Sitecore.Xdb.Common.Web   at Sitecore.Xdb.Common.Web.CommonWebApiClient`1.<ExecuteAsync>d__41.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 Sitecore.XConnect.Client.WebApi.CollectionBatchWebApiClient.<ExecuteBatch>d__11.MoveNext()
        
        Nested Exception
        
        Exception: System.Threading.Tasks.TaskCanceledException
        Message: A task was canceled.
        Source: mscorlib   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)   at Sitecore.Xdb.Common.Web.CommonWebApiClient`1.<ExecuteAsync>d__41.MoveNext()

        5244 19:38:16 ERROR Exception when executing agent processing/taskAgent
        Exception: Sitecore.Analytics.Processing.ProcessingTaskProviderException
        Message: An unexpected error occurred during pick of a deferred single-threaded task.
        Source: Sitecore.Analytics.Sql   at Sitecore.Analytics.Processing.SqlTaskDataProvider.<GetProcessingTaskUsingRoutineAsync>d__51.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 Sitecore.Analytics.Processing.SqlTaskDataProvider.<PickDeferredActionAsync>d__45.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 Sitecore.Analytics.Processing.TaskQueue.<TryGetNextAsync>d__2.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 Sitecore.Analytics.Processing.TaskAgent.<ExecuteCoreAsync>d__17.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 Sitecore.Analytics.Core.Agent.<ExecuteAsync>d__1.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 Sitecore.Analytics.Core.AsyncBackgroundService.<ExecuteAgentAsync>d__22.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 Sitecore.Analytics.Core.AsyncBackgroundService.<RunAsync>d__26.MoveNext()
        
        Nested Exception
        
        Exception: System.InvalidOperationExceptionMessage: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.
        Source: System.Data   at System.Data.Common.ADP.ExceptionWithStackTrace(Exception e)
        --- 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 Sitecore.Framework.TransientFaultHandling.Sql.SqlRetryHelper.<>c__DisplayClass18_0.<<OpenAsync>b__0>d.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 Sitecore.Analytics.Processing.SqlTaskDataProvider.<GetProcessingTaskUsingRoutineAsync>d__51.MoveNext()
        
        
        4172 19:38:16 ERROR Exception when executing agent aggregation/rebuildAgent
        Exception: System.InvalidOperationExceptionMessage: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.
        Source: System.Data   at System.Data.Common.ADP.ExceptionWithStackTrace(Exception e)
        --- 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 Sitecore.Framework.TransientFaultHandling.Sql.SqlRetryHelper.<>c__DisplayClass18_0.<<OpenAsync>b__0>d.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 Sitecore.Analytics.Aggregation.History.SqlRebuildProcessStateDataProvider.<GetStateAsync>d__2.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 Sitecore.Analytics.Aggregation.History.ReportingStorageManager.<ProcessRebuildAsync>d__27.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 Sitecore.Analytics.Aggregation.Data.Processing.RebuildAgent.<ExecuteCoreAsync>d__4.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 Sitecore.Analytics.Core.Agent.<ExecuteAsync>d__1.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 Sitecore.Analytics.Core.AsyncBackgroundService.<ExecuteAgentAsync>d__22.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 Sitecore.Analytics.Core.AsyncBackgroundService.<RunAsync>d__26.MoveNext()
        
        5244 19:38:16 ERROR Exception when executing agent aggregation/historyWorker
        Exception: Sitecore.Analytics.Aggregation.History.HistoryTaskProviderException
        Message: The unexpected error occurred during retrieval of the history task.
        Source: Sitecore.Analytics.Sql   at Sitecore.Analytics.Aggregation.History.SqlHistoryTaskDataProvider.<TryGetHistoryTaskAsync>d__18.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 Sitecore.Analytics.Aggregation.History.HistoryTaskManager.<GetTaskStatusAsync>d__18.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 Sitecore.Analytics.Aggregation.History.HistoryTaskManager.<IsActiveAsync>d__20.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 Sitecore.Analytics.Aggregation.History.HistoryTaskManager.<GetInteractionsBatchSchedulerAsync>d__17.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 Sitecore.Analytics.Aggregation.Data.Processing.InteractionHistoryWorker.<GetBatchSchedulerAsync>d__21.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 Sitecore.Analytics.Aggregation.Data.Processing.InteractionHistoryWorker.<ExecuteCoreAsync>d__20.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 Sitecore.Analytics.Core.Agent.<ExecuteAsync>d__1.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 Sitecore.Analytics.Core.AsyncBackgroundService.<ExecuteAgentAsync>d__22.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 Sitecore.Analytics.Core.AsyncBackgroundService.<RunAsync>d__26.MoveNext()
        
        Nested Exception
        
        Exception: System.InvalidOperationExceptionMessage: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.
        Source: System.Data   at System.Data.Common.ADP.ExceptionWithStackTrace(Exception e)
        --- 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 Sitecore.Framework.TransientFaultHandling.Sql.SqlRetryHelper.<>c__DisplayClass18_0.<<OpenAsync>b__0>d.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 Sitecore.Analytics.Aggregation.History.SqlHistoryTaskDataProvider.<TryGetHistoryTaskAsync>d__18.MoveNext()

Answer 1

This issue is finally resolved with the help of sitecore support!

The root cause was this error -

ERROR Could not update device detection database
Exception: System.NullReferenceException
Message: Object reference not set to an instance of an object.
Source: Sitecore.CES.DeviceDetection
at Sitecore.CES.DeviceDetection.Providers.FiftyOneDegrees.FiftyOneDeviceDetectionClient.GetNewerVersion()
at Sitecore.CES.DeviceDetection.Providers.FiftyOneDegrees.FiftyOneDeviceDetectionClient.DoUpdate(String serviceName, String& newDatabasePath, String& newDatabaseVersion)
at Sitecore.CES.DeviceDetection.Providers.DeviceDetectionClient.Update()
WARN [Experience Analytics]: Device detection component failed to resolve device information with error: Can not get device information: provider is not initialized. 
This error could be caused by the Device Detection database corruption in the "App_Data\DeviceDetection" folder or the absence of it there.

To resolve the above error, sitecore suggested -

This error could be caused by the Device Detection database corruption in the "App_Data\DeviceDetection" folder or the absence of it there.

In case there is some database downloaded it could be that some network issues occurred while the database is downloaded, so the file became corrupted. If so, please delete all files from the "App_Data\DeviceDetection" folder and restart the instance once more.

Sitecore will download the Device Detection database again.

We looked into the App_Data of processing instance but DeviceDetection folder was not present. On further investigation we found that proxy setting needs to be applied to the processing server so that it can download the DeviceDetection database.

After applying proxy settings, DeviceDetection database got downloaded successfully. After a day, data started showing in Experience Analytics and new visits were also getting tracked.

Thank you so much Sitecore Support and sitecore stackexchange users who helped in resolving the issue.

Answer 2

Based on the conversation in the comment section the issue is likely due to the fact that ProcessingEngine is not processing data from shard database. Few things to check:

1. Check windows service is running ProcessingEngine service
2. Make sure that there are no errors in ProcessingEngine log files.
3. Also check that IndexWorker service is running and no errors are present in log files. IndexWorker also write some analytic related data to index.

Update 1

Based on updated description of log files we can see there is nested exception OutOfMemoryException. This indicates that system cannot handle large amount of data. Two things you can check:

1. Clean %temp% folder from that on-prem instance where this error is coming from.
2. Remove debug attribute from web.config
3. Make application run on 64-bit which gives you more space on system memory.

Answer 3

Hard to give a definitive answer at the moment, but based on the discussion in the comments above, it is clear the reason for the missing data is due to failed aggregation tasks in the Sitecore processing instance.

The logs you have provided show a failure retrieving contact data from the xconnect collection instance. If these logs are repeating constantly in the processing instance then this is most likely the main issue.

Please review logs for xconnect collection instance and paste any exceptions here.

The other exceptions you posted relate to out of memory and also SQL. It is possible that these are simply as a result of the above. But worth double checking those areas to. Check for SQL connectivity from the boxes and also if sufficient ram (16g I believe) to meet sitecore specifications.