Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mmanimendra
New Member

How to limit number of times that PBI automatically retries a SQL query

Hello All

 

Is there a way to limit the number of times the PBI desktop automatically tries to re-connect to a SQL data source when the query is killed through a SQL job? We had a situation where one of our team members started a long running query to refresh a PBI desktop data model before leaving work. While this is running our overnight incremental data loaded has started. However we have a mechanism in place to kills all the other jobs that consumes the database before it starts the data load. This has killed the PBI query that tries to load the data to PBI desktop. However apparently it started to reconnect the database and tried again. This has happened until the morning and had a major issue with the data load. Apparently there are no limitation in terms of PBI ability to reconnect to a query when it was killed or disconnected.

 

Any help on this very much appreciatedks

Menaka M

Thanks

 

Menaka M

7 REPLIES 7
v-jiascu-msft
Employee
Employee

Hi @mmanimendra,

 

Could you please mark the proper answers as solutions?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
GilbertQ
Super User
Super User

Hi @mmanimendra

 

Could it possibly be that there was a scheduled refresh happening?

 

Also was the data model in Import or DirectQuery mode?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Thanks Gilbert

 

it wasnt a scheduled query but a long running query started by someone new to powerBI. Apperently DirectQuery mode has been used.

 

Please let me know if you need any additional details

 

Appriciate your help on this

 

Kind Reagrds

 

Menaka M

Hi @mmanimendra

 

To clarify, was it using DirectQuery?

 

Or was it import mode, where it was a long running query?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Hi Gilbert

 

my aplogies, it was actually a import mode. Query is using complex joins and sorts. due to this reason query was consuming resources from the server hevily. automatic process has killed (stoped) this query from running. but PBI seems to reconnect automatically.

 

Thanks

 

Menaka M

Hi Menaka,

 

How did your automatic process kill the query? I tried it by kill-transact-sql. It stopped immediately. There was an error in the Desktop. Please refer to the snapshot below.

How-to-limit-number-of-times-that-PBI-automatically-retries-a-SQL-query

BTW, the sessions started by Desktop could be more than one. 

And also the complex joins and sorts isn't a good idea in Power BI. It's better for the right tool to do the right thing.

 

Best Regards,
Dale

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

Hi @mmanimendra

 

No worries, glad you found out it was in Import mode.

 

And I honestly have never tried to kill a TSQL query that is going via the Gateway. I could suggest possibly putting a time on how long before it times out in order to limit it in the future?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors