Microsoft SQL: Execution Timeout Expired Error while refresh in Power BI Service

[Anonymous]

Hello, I have one report, which dataset is SQL Server based. When I start on-demand refresh for that report in Service, sometimes it was completed, but sometimes it gives me an error like the below,
"Microsoft SQL: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding."
So, I have checked a few suggestions on that error here and I have applied also. Suggestions like, Adding Command Timeout at Source level in Power Query, and also I have added Seconds in SSMS.
But after that also I'm facing this SQL Execution Timeout Error while Refresh, few times refresh was completed, but randomly it gives me this error.

Can anyone face the same error again n again randomly, and if you solved that? Please help me out with that Error.
Thanks.

[v-yueyunzh-msft · Accepted solution]

Hi , @Anonymous 

According to the error information you provide, you encounter a random timeout error during data refresh, which occurs once or twice a day.actually for the SQL Server connctor in Power BI, it has a default timeout value limitation for 10 minutes, this is mentioned in the official document of Power BI SQL Server connector:

Power Query SQL Server connector - Power Query | Microsoft Learn

If you frequently met this SQL Server timeout issue during the dataset refresh or query execution process, you can try to manually set the timeout value for the SQL Server connector property in the original .pbix file of this report and dataset, for the reason why the refresh frequently face timeout error while automatically resolved after few minutes, I think this is depends on the performance of the data source. For the SQL Server, the more queries received at the same time, the slower the execution and processing of these queries will be. If the query completion time fluctuates around your default timeout time, the query will sometimes succeed and sometimes timeout, so I think the issue may disappear when you try to set the timeout inminutes property to “120” because this is maxium supported timeout inminutes property vaule.

Then you can apply the queries and republish the .pbix file to Power BI service to re-configure the gateway connection to check the dataset refresh again.

Based on your issue description, if you have set the timeout inminutes property to “120” but still met some "Timeout" error message randomly, please expand the latest refresh detailed error message and provide to this case reply for me to check if your refresh duration is more than 120 min which caused the refresh failure.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[Anonymous]

Thanks for your valuable help.
But, I have already tried this way also, not just this way, I have added Execution Timeout Seconds in SSMS also. But still, this SQL Execution Timeout Error occurs randomly. Sometimes refresh was completed, and sometimes it fails.
I'm still trying to find out any other reason for that Execution error.
Thanks