We recently performed an upgrade from Sitecore 8.2 to 9.0.1 and at first, all was working smoothly. We installed everything in a modified XP3 configuration. After a small incident where SQL Server was down for some time, we noticed that analytics aggregation stopped running once the SQL Server was back up. Analytics was still being collected, though, and we determined this by looking at the “InteractionLiveProcessingPool” table in the “Processing.Pools” database. It was growing in size (records were continually being added) but the Sitecore agents for processing were not taking any action, so it did not reduce in row count. Usually, if everything is running smoothly, you should notice the records in this table reduce down to zero.
There were no errors in the logs and it was really difficult to debug the issue, so I reached out to the Sitecore Support team for some additional assistance. It was a tough one for them to figure out as well, and we ended up having to use JetBrains dotTrace tool to figure out what was going on.
In the end, the Sitecore Support team found out that the Device Detection Database update was failing due to the lack of a hotfix you can find here. The agents were basically hung after that failure and wouldn’t get around to processing the analytics information. Once we installed the hotfix, we started seeing the records in the “InteractionLiveProcessingPool” table reduce until they reached zero. Taking a look at Experience Analytics also showed that all the data was processed (and continued to be processed).