Power BI Gateway Service – “The on-premises data gateway service” is offline

Background

Application Subject Matter Expert ( SME ) called this morning to report that his application is down.

Error Message

Here is the error message


Couldn't load the data for this visual
The gateway is either offline or could not be reached.
Please try again later or contact support. If you contact support, please provide these details.
Error CodeDM_GWPipeline_Client_GatewayUnreachable
Activity ID5a763a2d-d357-34ab-5396-ec568baae17a
Correlation ID7b1ee57b-c957-d313-125a-08b550e3ab1b
Request ID4bcbe543-5cd3-9787-8ad4-47cdb93842f4
Time    Tue Jan 17 2017 09:57:16 GMT-0800 (Pacific Standard Time)
Version13.0.1700.1003
Cluster URI

Diagnostic

Control Panel – Services Applet

Looked for the Gateway Service and in our case it is on the same box as Our SQL Server Engine.

Using the services applet in Control Panel, confirmed that the service is indeed stopped.

Quickly restarted it.

relevantservices

 

Why is it down?

MS SQL Server

ErrorLog

We checked MS SQL Server and know that the errorlogs were recycled on Jan 14th, 2017.

 

Event Viewer

System Log

Filter
Filter – Custom Range

Let us set our date range to encompass Jan 14th, 2017.

And, so 12 AM to 12:55 PM on Jan 14th, 2017.

customrange

 

Filter – Filter Current Log

Here is what our filter looks like once we narrowed on Jan 14th.

filtercurrentlogforjune142017

Explanation

  1. Filter Out events that are causing a lot of noises.
    • In our case most of the noises were Events 140 & 157
    • Place a minus in front of an event to filter them out
    • Event ID = 140
      • Event ID :- 140 NTFS Warning
        The system failed to flush data to the transaction log. Corruption may occur in VolumeId:<> DeviceName: \Device\HarddiskVolume<>.(A device which does not exist was specified.).
    • Event ID = 157
      • Event ID :- 157 “Disk # has been surprise removed”

 

Events
Events Image

Here are the events ordered by Level..

eventssystemforjune142017-20170117-0615pm

Events Image
Level Date and Time Source  Event ID  General
 Error  1/14/2017 1:25:31 AM  Service Control Manager  7000  The SQL Server Integration Services 12.0 service failed to start due to the following error:
The service did not respond to the start or control request in a timely fashion.
 Error  1/14/2017 1:26:51 AM  Service Control Manager  7000 The On-premises data gateway service service failed to start due to the following error:
The service did not respond to the start or control request in a timely fashion.
 Error  1/14/2017 1:25:31 AM  Service Control Manager  7009 A timeout was reached (30000 milliseconds) while waiting for the On-premises data gateway service service to connect.

 

 

Explanation:

  1. Two of our services are timing out.  And, those are:
    1. SQL Server Integration Services
    2. And, On-Premises Data Gateway Service
  2. Two types of events are listed
    • Event ID :- 7000
      • Service Start Timeout
    • Event ID :- 7009
      • Timeout was reached

 

Remediation

  1. Change Service Mode from Automatic to “Automatic Delayed Start
  2. Change Service Dependency
  3. Write a start service Script and schedule through “Task Scheduler

 

Change Start Mode from Automatic to “Automatic Delayed Start”

servicestartuptype

 

Add Service Dependency

servicedependecy

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s