Scheduled refresh failed seemingly random

[Giuseppe_Aino]

In a sneaky way, from an unspecified date onwards, the data update schedules of the report in import mode fail randomly.

The error returned is:

SessionID: ......
[0] -1055784934: DataSource.Error: An error happened while reading data from the provider: 'Invalid operation.
The connection is closed.'. DataSourceKind = SQL. DataSourcePath = host_serverName,port;DB_Name.
L'eccezione è stata generata dall'interfaccia di IDataReader.
Esaminare il messaggio di errore e la documentazione del provider per altre informazioni e azioni correttive.

Note that there is no error when refreshing data from PowerBI Desktop, It's the only way that never gives problems.

My Workaround

In order to mitigate the problem I added a schedule close to the initial canonical one. The result is that sometimes the first one fails and sometimes the second one but in total I increased the successful updates.

Resolution attempts

I have disabled the use of TCP/IPv6 from both virtual network adapter ... but without success

System Configuration

• Windows Server 2016 Standard, 64 bit
• (Power BI on-permises) Power BI Report Server and PBIDesktopRS_x64 January 2023
• MS SQL Server 2017 Enterprise Ed. 64 bit (SQL Server on different machine [Linux Red Hat 8.3]).

I thank in advance anyone who pays attention to my problem and tries to give me some advice.

Giuseppe

[Giuseppe_Aino · Accepted solution]

Solved, the problem was not on PBRS.
Special thanks to Carson Jian who pointed me in the right direction.

The problem became evident running the main query from SQL Server Management Studio. At the first attempt always ok, 1 second of execution on 93K records, at the next attempt, 40K records extracted and then "timeout" with the error:

"A transport-level error has occurred when receiving results from the server..."

After querying the same database from another server I found no problems so the cause is on a server configuration on which PBRS runs.

The key was the description of the error: "A transport-level error has occurred...". After an internet surfing I found the solution by changing to off the auto scaling abilities of the network stack, from command prompt (solution source here )

netsh interface tcp set global autotuning=disabled

Obviously this choice has pros and cons... surely among the pros there is the solution to my problem 😁

A request to PBRS developers, please report the error stack so that troubleshooters can understand and not wander randomly in the universe of possibilities 🙂

Thank you.

[Anonymous]

Hi,@Giuseppe_Aino .I am glad to help you.

It looks like you have an intermittent refresh failure issue when refreshing pbix report files in import mode in Power BI Report Server. Sometimes the refresh succeeds but sometimes it fails

Power BI Report Server, unlike Power BI Service, the success of a Scheduled Refresh operation depends on the server's performance configuration.
If the connection to the data source is down or the server is out of memory while performing a Scheduled Refresh, this will result in a Scheduled Refresh failure.

According to your screenshot, there is an error message mentioned:
"Invalid operation. The connection is closed.", which indicates that the connection is closed while reading the data. Please make sure that the connection to the data source (SQL Server) is stable and there are no network interruptions or timeout issues
1. Check that the SQL database connection is OK (server is online)
Try to optimize the report query request.
Some users have mentioned that the problem may occur when the report query is too complex, and they have solved the problem by simplifying the query and shrinking the report query. Although your report refresh problem is intermittent: there are records of successful refreshes, which indicates that the configuration of your data source credentials is fine, if you can optimize from the report itself, you can  reduce the memory pressure on the server when the report server performs Scheduled Refresh, and reduce the number of times that Scheduled Refresh goes wrong.
URL:

Invalid operation. The connection is closed or Query too complex | Microsoft Community Hub

2. Re-plan your Scheduled Refresh program
Avoid setting up too frequent Scheduled Refresh (e.g., every 5 or 30 minutes), which may cause the system to be overloaded and cause errors. Try to set up longer intervals between report Scheduled Refreshes and make sure that you do not perform Scheduled Refreshes on more than one report at the same time within a short period of time. (Ensure that the server memory is available)

3. Check other related configurations, and if conditions allow, try to configure load balancing for the report server.
This helps to reduce or lift the number of report Scheduled Refresh failures.
Check your report server log files, which hold more detailed information about task execution.
trouble shooting URL:
Troubleshoot scheduled refresh in Power BI Report Server - Power BI | Microsoft Learn
Solved: Report Server Scheduled refresh issues - Microsoft Fabric Community
Power BI report scheduled refresh in Power BI Report Server - Power BI | Microsoft Learn

load balancing URL:
Configure a report server on a network load balancing cluster - SQL Server Reporting Services (SSRS)...
Power BI Report Server Scale Out With Load Balancing | Key2 Consulting

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Giuseppe_Aino]

Thank you Carson Jian for your help much appreciated but I still haven't been able to solve it.

It is unlikely that it could be a performance issue, the data refresh takes:

• 12 seconds form PowerBI Desktop;
• 2 minutes from success scheduled refresh (based on "RSPoerBI_*.log" file).

The PBIX file is 10MByte large, the Server RAM is 32GByte of which 10 are free.
The Server hosting the PowerBI Report Server is unlibered and has only one report with associated schedule.

I did the following test in order to understand if there is a problem in the database connection when the scheduled refresh starts:

I implemented and delivered a new report to the customer in "DirectQuery" mode (which I will call 'DQ') this  works on the same database tables as the report in "Import" mode (which I will call 'I'), well the data refresh takes only a few seconds in first opening. I can exclude performance issues or datasources down. The two report ('DC' and 'I') are similar, same pages with same visuals

To test the same conditions at the same time, when the scheduled refresh update start (for 'I' report that will fail after 10 or 15 minutes) I opened the 'DQ' report and I have no errors or problem in datasource loading even with performances. 

About the application logs:

• In the "RSPortal_*.log" file there are no errors, I am referring to the loading of the report in DirectQuery version.
• In the "RSPoerBI_*.log" file there are only the Scheduling errors of the report in Import mode. (I attach part of the log, see log rows for thread |75|)

2025-01-14 16:30:09.5151|INFO|75|Handling event with data: TimeEntered: 14/01/2025 15:30:00, Type: Event, SessionId: 8abf7b36-4883-4730-94a9-61e113f6e0dc, EventType: DataModelRefresh
2025-01-14 16:30:09.5151|INFO|75|Processing Data Model Refresh: SessionId: 8abf7b36-4883-4730-94a9-61e113f6e0dc, Status: Starting Data Refresh.
2025-01-14 16:30:09.5151|INFO|75|Processing Data Model Refresh: SessionId: 8abf7b36-4883-4730-94a9-61e113f6e0dc, Status: Starting Retrieving PBIX AsDatabaseInfo.
2025-01-14 16:30:09.5351|INFO|72|Finding model for refresh sessionId=8abf7b36-4883-4730-94a9-61e113f6e0dc, catalogId=8a83f034-65ff-4336-b5be-2813da20415c, lastCatalogItemUpdate =17/12/2024 17:59:06, lastModelUpdateinCatalog=14/01/2025 15:52:33, resolved to database name=8a83f034-65ff-4336-b5be-2813da20415c_-1528882689, wasInMemory=True
2025-01-14 16:30:09.5401|INFO|75|Processing Data Model Refresh: SessionId: 8abf7b36-4883-4730-94a9-61e113f6e0dc, Status: Starting Verifying all the data sources are embedded.
2025-01-14 16:30:09.5401|INFO|75|Processing Data Model Refresh: SessionId: 8abf7b36-4883-4730-94a9-61e113f6e0dc, Status: Starting Verifying connection strings are valid.
2025-01-14 16:30:09.5401|INFO|75|Processing Data Model Refresh: SessionId: 8abf7b36-4883-4730-94a9-61e113f6e0dc, Status: Starting Streaming model to Analysis Server.
2025-01-14 16:30:09.5852|INFO|79|LoadDatabaseAsync: Successfully EnsureDbLoaded clientSessionId=8abf7b36-4883-4730-94a9-61e113f6e0dc, databaseName=8a83f034-65ff-4336-b5be-2813da20415c_-1528882689, modelId=1602001899, alreadyLoaded=True
2025-01-14 16:30:09.7602|INFO|75|Processing Data Model Refresh: SessionId: 8abf7b36-4883-4730-94a9-61e113f6e0dc, Status: Starting Refreshing the model.
2025-01-14 16:30:09.9052|INFO|75|GetDataSourcesCollection: sessionId=8abf7b36-4883-4730-94a9-61e113f6e0dc, databaseName=8a83f034-65ff-4336-b5be-2813da20415c_-1528882689, PowerBIDataSourceVersion=PowerBI_V3
2025-01-14 16:30:09.9302|INFO|75|Model Refresh: sessionId=8abf7b36-4883-4730-94a9-61e113f6e0dc, isRefreshActive=True, incoming databaseName=8a83f034-65ff-4336-b5be-2813da20415c_-1528882689, databaseId=1602001899, activeModel.DatabaseName=8a83f034-65ff-4336-b5be-2813da20415c_-1528882689
.
.
.
.
.
2025-01-14 16:46:27.1605|INFO|75|Model Refresh: sessionId=8abf7b36-4883-4730-94a9-61e113f6e0dc, isRefreshActive=False, incoming databaseName=8a83f034-65ff-4336-b5be-2813da20415c_-1528882689, databaseId=1602001899, activeModel.DatabaseName=8a83f034-65ff-4336-b5be-2813da20415c_-1528882689
2025-01-14 16:46:27.1605|ERROR|75|Error Processing Data Model Refresh: 
SessionId: 8abf7b36-4883-4730-94a9-61e113f6e0dc, 
Status: Error Failed to refresh the model, 
Exception Microsoft.AnalysisServices.OperationException: Failed to save modifications to the server. 
Error returned: 'DataSource.Error: An error happened while reading data from the provider: 'Invalid operation. The connection is closed.'. 
DataSourceKind = SQL. DataSourcePath = hostname,portNr;databaseName. 
L'eccezione è stata generata dall'interfaccia di IDataReader. Esaminare il messaggio di errore e la documentazione del provider per altre informazioni e azioni correttive.'.
   at Microsoft.AnalysisServices.Tabular.Model.SaveChangesImpl(SaveOptions options)
   at Microsoft.PowerBI.ReportServer.AsServer.TOMWrapper.RefreshModel(Database database)
   at Microsoft.PowerBI.ReportServer.AsServer.AnalysisServicesServer.RefreshDatabase(String databaseName, IEnumerable`1 dataSources, IEnumerable`1 parameters, String clientSessionId)
   at Microsoft.PowerBI.ReportServer.WebHost.EventHandler.AnalysisServicesDataRefresh.RefreshDatabase(AsDatabaseInfo asDatabaseInfo)
   at Microsoft.PowerBI.ReportServer.WebHost.EventHandler.DataRefreshScope.<>c__DisplayClass34_0.<ExecuteActionWithLogging>b__0()
   at Microsoft.PowerBI.ReportServer.WebHost.EventHandler.DataRefreshScope.<ExecuteFuncWithLogging>d__33`1.MoveNext()

[Anonymous]

Hi,@Giuseppe_Aino .Thank you for your reply.

Error returned: 'DataSource.Error: An error happened while reading data from the provider: 'Invalid operation. The connection is closed.'. 
DataSourceKind = SQL. DataSourcePath = hostname,portNr;databaseName.

Perhaps you can try the following:
Increase the timeout a bit
Set the source permissions level of the report

URL:
Power BI Scheduled Refresh Error An error happened... - Microsoft Fabric Community
Solved: Timeout at Microsoft.AnalysisServices.Tabular.Mode... - Microsoft Fabric Community
 I think the probable cause is still due to poorly configured Scheduled Refresh causing the refresh operation to sometimes fail and sometimes succeed
Please try to re-upload the problematic pbix file to the power bi report server and reset the data source credentials to ensure that you can access the SQL data source normally, try to stagger the Scheduled Refresh operation between multiple reports, and don't execute multiple Scheduled Refreshes in a short period of time at short notice.

If you still haven't solved the problem, you can create a Microsoft support ticket , for more help.

The Link of Power BI Support:

Microsoft Fabric Support and Status | Microsoft Fabric