Some scheduled refreshes are failing at midnight

[iron_dinges]

For the last few days, even morning I open my email to see 3-4 emails from Power BI indicating that a dataset refresh has failed.

These are all scheduled to refresh daily.

Manually refreshing the dataset works fine - there are no issues with the dataset.

I typically get two types of error messages:

1. 

Failure details: The last refresh attempt failed because of an internal service error. This is usually a transient issue. If you try again later and still see this message, contact support.

{"error":{"code":"DM_GWPipeline_Client_GatewayUnreachable","pbi.error":{"code":"DM_GWPipeline_Client_GatewayUnreachable","parameters":{},"details":[],"exceptionCulprit":1}}} Table: TableName.

2. 

Failure details: Your data gateway is offline or could not be reached. Ensure the computer running the gateway is on during the refresh period.

The gateway is not offline at the time, as I have many other datasets that refresh successfully at that same time.

Is this a transient issue with the service itself?

[collinq]

Hi @iron_dinges ,

There might be a few different reasons for this.  One of the things that I have seen when this error is "consistent" within a time frame is that the system internally is doing something - like a backup of the server(s) or the database(s) is occuring and you are running into timeouts/sql locks because the backup is running and taking all the resources.  Another thing I have seen is that something in the organizational network is resetting and breaking all the existing connections (ie. a router or security or firewall setting that clears all the connections at midnight to reset for the day).  This all might include the server that is housing the gateway - does it go offline for a bit every night?  Is it a VM and a snapshot is taken every night at that time?

Let us know.

I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!

[iron_dinges]

Hi @collinq ,

The database being queried is not being backed up (it is a transactional replication subscriber).

The server is a VM that gets backed up at about 20:00 PM local time (GMT+2), which completes in a few minutes.

As far as I know there are no router or firewall settings that clear connections, however I will check with my infrastructure team.

The server never goes offline - a have many reports that refresh using this same server as the data source, and they complete successfully.

Is the issue perhaps due to the amount of datasets I am refreshing at the same time - maybe I'm getting some locks on SQL side?

I have about 60 reports, most of which have individual datasets.

If that is the case, is there a way to configure the daily refresh time on Power BI service?

I know that I can set additional refresh times, but as far as I know the daily refresh time is always midnight. Is it possible to change this so that I can space out my refreshes? I just had a look at the configuration and noticed there is a "Time zone" setting - if I change this value, will that change the time that my report refreshes? Will this impact anything else?

[collinq · Accepted solution]

Hey @iron_dinges ,

yes, you could have too many reports hitting the exact same SQL area at the same time and causing your own traffic jam.  To schedule the time, and to stagger these out a bit, go to the dataset, click on the elipses and select the "Schedule Refresh" option and change it there.

I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!

[iron_dinges]

Hi @collinq ,

Thank you for the advise, I will do just that.