cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Scheduled Refresh Fails Intermittantly

We have an issue with scheduled refreshes not working occasionally.  This is ocurring with two different on-premise data gateways that connect to two different SQL Servers and databases which are part of an Availability Group.  The error is basically the same each time.  The Table value changes to one of the queries we have in the dataset file.  The Pre-Login and Handshake values change as well sometimes.   

 

Part of Error message:

Underlying error code: -2147467259 Table: "TableName".  
Underlying error message: Microsoft SQL: Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=133; handshake=14961;

We have added connection timeout of 10 minutes to each of the queries in the dataset file. but the issue persists.

 

This article below seems close to what we are encountering but is not in context of PowerBI. 

https://stackoverflow.com/questions/15488922/connection-to-sql-server-works-sometimes

 

I have scanned the web and this forum, but I have not found a fix. Has anyone encountered the same issue?   

5 REPLIES 5
venal
Memorable Member
Memorable Member

@Anonymous 

Is the gateway is latest version. (please try to open the Gateway and sign-in with the right credentials)

If it is the latest Gateway version, please check the dataset settings with Gateway Connection and Data Source Credentials.

 

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

If this post was helpful may I ask you to mark it as solution and give it some kudos?

ibarrau
Super User
Super User

Hi, can you please try adding a timeout like 120 minutes for each query in edit queries. That is the power bi refresh limit from service. If that much doesn't work then you have a problem because 10 minutes is not so much for big tables. The engine could be busy at that time and work upload the data under 10 minutes.

 

I don't think is a power bi problem it looks like an engine problem getting the query results very slow.

 

Regards,

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


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

Happy to help!

LaDataWeb Blog

Anonymous
Not applicable

Thank you for the suggestion.   The failure error occurs within 20-30 seconds so the 10 minute timeout should suffice I would think. 

If the error is in 20-30 seconds is a connection problem. The gateway is having trouble to connect to the data source. Please be sure you have the gateway output ports opened and to test a connection from the gateway administration and from the gateway computer to garantee you can actually connect the data.

 

Regards,


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

Happy to help!

LaDataWeb Blog

Anonymous
Not applicable

Thanks, but have run the gateway port test.  All came back fine.  I have additional logging turned on as well.  Unfortunately it the gateway logs show the same information as the Power BI "Refresh History" modal.  There is no rhyme or reason to the failures and am thinking at this point it is something at the network layer or an AV monitor/scanner on the server blocking communication out.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors