RVY200801: Connection Timeout Expired error messages while generating DataHub Reports

Symptoms: 

Data Hub gets Connection Timeout Expired errors while generating reports.

Please first check in Event Viewer → Windows Protocols → Application if there are Information messages like below:

„Parallele Wiederholung "shutdown" für die Datenbank "{DBNAME}" mit Workerpoolgröße [2]“.

If this is the case, your DB has AUTO_CLOSE ON and is shutting down before the report is even generated. This can occur if you have SQL Express installed, but it is not the only case. It can also happen when you restore a backup from which the DB’s AUTO_CLOSE configuration was set to ON.

Solution: 

AUTO_CLOSE must be turned OFF.

Microsoft is referring that AUTO_CLOSE for DB’s should be set OFF.

“This rule checks whether the AUTO_ CLOSE option is set OFF. When AUTO_CLOSE is set ON, this option can cause performance degradation on frequently accessed databases because of the increased overhead of opening and closing the database after each connection. AUTO_CLOSE also flushes the procedure cache after each connection.”

Reference:

https://docs.microsoft.com/en-us/sql/relational-databases/policy-based-management/set-the-auto-close-database-option-to-off?view=sql-server-ver15

Instructions:

Run the query below, change the [DBNAME] with the name used for "DataHub_{Tenant}" DB name.

USE [master]

GO

ALTER DATABASE [DBNAME] SET AUTO_CLOSE OFF WITH NO_WAIT;

GO