PowerBi report refresh timing out

[sydneycroud]

We have a report that will always refresh successfully when refreshed from the desktop. However, when we have the refresh scheduled, it is constantly failing, not 100% of the time but maybe 90% of the time. Below is a sample of the log showing the failure.

It seems to indicated a timeout error but it's not based on any server or report configurations, as those are at 15 minutes. In addition, as you can see, the timeout is happening within a minute of the report refresh launching. When I have tracked what is happening in the database, I see the report initiating connections, running the required queries and getting the results back in a timely manner.

I opened a ticket with msft but they have not been of any assistance and I am at the end of my rope.

A couple of questions. One is, is the timeout happening in the PowerbiReportServer database, since it always works when the refresh is done from the desktop, bypassing this database? I have tried to look there in the catalog table and SQL logs, but have never found anything of help. I have combed the forums as well, and don't see any solution for this.

It is on premise and the SQL server where the PowerbiReportServer database is the same that hosts the database being queried. Any help would be deeply appreciated.

EventType: DataModelRefresh
2021-02-09 11:18:07.8314|INFO|40|Processing Data Model Refresh: SessionId: d1571892-238c-4c7e-928d-f1312d454c4e, Status: Starting Data Refresh.
2021-02-09 11:18:07.9877|INFO|40|Processing Data Model Refresh: SessionId: d1571892-238c-4c7e-928d-f1312d454c4e, Status: Starting Retrieving PBIX AsDatabaseInfo.
2021-02-09 11:18:08.2846|INFO|40|Processing Data Model Refresh: SessionId: d1571892-238c-4c7e-928d-f1312d454c4e, Status: Starting Verifying all the data sources are embedded.
2021-02-09 11:18:08.5814|INFO|40|Processing Data Model Refresh: SessionId: d1571892-238c-4c7e-928d-f1312d454c4e, Status: Starting Verifying connection strings are valid.
2021-02-09 11:18:08.8002|INFO|40|Processing Data Model Refresh: SessionId: d1571892-238c-4c7e-928d-f1312d454c4e, Status: Starting Streaming model to Analysis Server.
2021-02-09 11:18:09.5033|INFO|40|Processing Data Model Refresh: SessionId: d1571892-238c-4c7e-928d-f1312d454c4e, Status: Starting Refreshing the model.
2021-02-09 11:18:19.3317|INFO|40|Processing Data Model Refresh: SessionId: d1571892-238c-4c7e-928d-f1312d454c4e, Status: Starting Removing credentials from the model.
2021-02-09 11:18:19.9567|INFO|40|Processing Data Model Refresh: SessionId: d1571892-238c-4c7e-928d-f1312d454c4e, Status: Starting Saving model to the catalog.
2021-02-09 11:18:43.0369|ERROR|40|Error Processing Data Model Refresh: SessionId: d1571892-238c-4c7e-928d-f1312d454c4e, Status: Error Failing saving model to the catalog, Exception 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.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest)
at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
at System.Data.SqlClient.SqlInternalTransaction.Commit()
at System.Data.SqlClient.SqlTransaction.Commit()
at Microsoft.ReportingServices.CatalogAccess.Streams.ExtendedContentWritableStream.Flush()
at Microsoft.PowerBI.ReportServer.WebHost.EventHandler.AnalysisServicesDataRefresh.SaveDatabaseToCatalog(String databaseName, Guid catalogItemId)
at Microsoft.PowerBI.ReportServer.WebHost.EventHandler.DataRefreshScope.<>c__DisplayClass38_0`2.<ExecuteStepWithExecLog>b__0()
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()
ClientConnectionId:66ee1b33-29f4-4cef-8fd8-b58886b32b29
Error Number:-2,State:0,Class:11
2021-02-09 11:18:43.2088|ERROR|40|Error Processing Data Model Refresh: SessionId: d1571892-238c-4c7e-928d-f1312d454c4e, Status: Error Failed Data Refresh, Exception 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 Microsoft.PowerBI.ReportServer.WebHost.EventHandler.DataRefreshScope.ExecuteActionWithLogging(Action methodToExecute, String description, String localizedDescription, String messageInFailure, RefreshInfo refreshInfo, DataAccessors dataAccessors, ReportEventType operation, Boolean isDataRetrieval, Boolean showInExecutionLog, Boolean sendToTelemetry)
at Microsoft.PowerBI.ReportServer.WebHost.EventHandler.AnalysisServicesDataRefresh.RefreshData(RefreshInfo refreshInfo)
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()

[v-deddai1-msft]

Hi @sydneycroud ,

I can't reproduce your issue. I suggest go to https://support.powerbi.com. Scroll down and click "CREATE SUPPORT TICKET".

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[sydneycroud]

Thank you for the suggestion, but that's why I came to the forums, I did open a support ticket, but they haven't been any help, so after six weeks I came here as my last shot at fixing this.

[v-deddai1-msft]

Hi @sydneycroud ,

If your query needs more than the default 30 seconds, you might want to set the commend timeout higher:

And please also refer to https://blog.sqlauthority.com/2016/01/26/sql-server-timeout-expired-the-timeout-period-elapsed-prior...

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[sydneycroud]

Thank you for your suggestion, Demon.

However, I had adjusted that setting to 30 minutes, even though the query typically finishes in less than two minutes. It had no impact and the refresh continues to fail.

As I mentioned, when monitoring the refresh, I see the queries run and they complete processing. It appears the timeout is occurring when it is saving the results of the queries back to the model, which I assume means updating the actual report.

[allyfLinek]

Olá, eu estou com o seu mesmo problema, encontrando o mesmo erro que você mostrou nos logs, é incrivel, pois isso começou do absoluto nada na versao de may de 2024. você solucionou isso? é incrivel como tentei absurdamente achar uma solução para isso, tantos nos forums, nos logs, no banco do report server, nas configurações, no sql agent, mas nada funciona por enquanto. espero que você tenha encontrado alguma solução.