Forum Discussion

JanRambo's avatar
JanRambo
Regular Visitor
8 years ago
Solved

MS SQL server timeout

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

 

 

Need Help
  • JanRambo's avatar
    JanRambo
    8 years ago

    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

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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.

    On-prem-data-gateway-UI-logs

    Installer logs

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

    Configuration logs

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

    On-premises data gateway service logs

    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.

    On-prem-data-gateway-event-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

  • GilbertQ's avatar
    GilbertQ
    Icon for Super User rankSuper User
    8 years ago
    Hi there, what have you set the Timeout to on your SQL Server Data Connector? I have got my defaulted to 20 minutes.
    • JanRambo's avatar
      JanRambo
      Regular Visitor
      8 years ago

      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's avatar
        dung-anh
        Icon for Advocate I rankAdvocate I
        8 years ago

        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