MS SQL server timeout

[JanRambo]

Hello PBI team,

we have following scenario:

a) On-Premise Data Gateway is installed on an app server,

b) MS SQL as a source data is geographically elsewhere,

c) 3 reports published on PBI service, each max 3 MB:

1 report with 3 queries

2 report with 4 queries

3 report with 1 query

All ports from app server allowed to Azure, as well Azure data center reachable from SQL server. App server allowed to communicate on port 1433 towards SQL server.

All reports (data sets) had been scheduled to refresh for a moth without a single failure. Then suddenly from 1st of March, only 1 report is being updated every day, also without a single failure, #3 with 1 query. The other ones always fail:

- when scheduled, timeout happens after around 55 mins

- when refreshed on demand from PBI service, timeout happens between 10-20 mins.

Type or error:

Microsoft SQL: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

What we tried:

- republishing 2 reports, still the same.

We are really frustrated, as network team says all is good.

Any ideas please, what went wrong?

Thank you very much for help!

Jan Rambousek

Underlying error code:	-2147467259 Table: Query1.
Underlying error message:	Microsoft SQL: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
DM_ErrorDetailNameCode_UnderlyingHResult:	-2147467259
Microsoft.Data.Mashup.ValueError.Class:	11
Microsoft.Data.Mashup.ValueError.DataSourceKind:	SQL
Microsoft.Data.Mashup.ValueError.DataSourcePath:	xxxxxxx
Microsoft.Data.Mashup.ValueError.Message:	Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Microsoft.Data.Mashup.ValueError.Number:	-2
Microsoft.Data.Mashup.ValueError.Reason:	DataSource.Error
Cluster URI:	WABI-US-NORTH-CENTRAL-redirect.analysis.windows.net
Activity ID:	7c3d4321-07d5-4f14-81b4-a99ecf2ae78a
Request ID:	22b38473-5eb1-4167-9af2-04fc727d48af
Time:	2018-03-14 10:24:48Z

[GilbertQ]

Hi there, what have you set the Timeout to on your SQL Server Data Connector? I have got my defaulted to 20 minutes.

[JanRambo · Accepted solution]

Hi,

we have checked on the SQL server timeout and it set far beoynd our timeout error. We have one more time republished all reports and manually set timeout to 120min prior to publishing to PBI service and it worked. Very strangely, now data sets are refreshed normally without hitting original timeout period of around 55 mins.

I just hope this will be stable now 🙂

Thanks for help!

Jan

[dung-anh]

Hi @JanRambo

How do you:

1/check the SQL Server timeout?

2/ and set manually set timeout of the report to 120min prior to publishing to PBI service?

Thanks in advance,

Dung Anh

[JanRambo]

Hi Dung Anh,

we have an internal team supporting our SQL server, so I can not help with that, sorry.

ad 2/ You need to edit each query settings, before publishing to PBI service.

[DAN]

Thanks a lot @JanRambo!

Dung Anh

[v-shex-msft]

Hi @JanRambo,

Please refer to below link to share some details contents about your issue:

Troubleshooting the on-premises data gateway

Spoiler

Tools for troubleshooting

Collecting logs from the gateway configurator

There are several logs you can collect for the gateway, and you should always start with the logs. The simplest way to collect logs after installing the gateway is through the user interface. In the On-premises data gateway user interface, select Diagnostics and then select the Export logslink near the bottom of the page, as shown in the following image.

Installer logs

Copy

%localappdata%\Temp\On-premises_data_gateway_*.log

Configuration logs

Copy

%localappdata%\Microsoft\On-premises Data Gateway\GatewayConfigurator*.log

On-premises data gateway service logs

Copy

C:\Users\PBIEgwService\AppData\Local\Microsoft\On-premises Data Gateway\Gateway*.log

Event Logs

The On-premises data gateway service event logs are present under Application and Services Logs.

Fiddler Trace

Fiddler is a free tool from Telerik that monitors HTTP traffic. You can see the back and forth with the Power BI service from the client machine. This may show errors and other related information.

Refresh History

When using the gateway for scheduled refresh, Refresh History can help you see what errors have occurred, as well as provide useful data if you should need to create a support request. You can view both scheduled, as well as on demand, refreshes. Here is how you can get to the Refresh History.

1. In the Power BI navigation pane, in Datasets, select a dataset > Open Menu > Schedule Refresh.

   

2. In Settings for... > Schedule Refresh, select Refresh History.

   

   

E.g.

Refresh history, local gateway log, trace session...

Regards,

Xiaoxin Sheng