Use your database to cancel Power BI refreshes
Say you accidentally clicked the refresh button and now Power BI is hitting your database in the middle of the day. There is no option to stop it in the Service at the time of writing, and the option provided by Microsoft is to use its API to do the job. Well, if your dataset is sourcing a database and you have the right access level, then you can go in and cancel the queries submitted by the Service.
Let me show you how to accomplish this with SQL Server / T-SQL.
Please note the examples will use dedicated SQL Pools available with Azure Synapse Analytics, but the difference to other systems is around the system tables used.
Context for Power BI refreshes
When Power BI hits your database for a refresh, it will create a) a session for the authenticated connection and b) a query for each table to refresh. You can use these to forcefully stop the refresh.
Solution: stop sessions
Dedicated SQL Pools in Azure have sys(tem) tables dedicated to sessions and queries.
You can get a list of active sessions in your pool using
SELECT * FROM sys.dm_pdw_exec_sessions WHERE status = 'Active'
In the instance above I had ten active sessions — note how seven of those were coming from Power BI in the app_name column. Yup I ended up with a lot of simultaneous active refreshes by mistake and I really needed to stop them.
The first option is to KILL the sessions. In my case I knew I wanted all stopped, but if you have valid refreshes in the mix then proceed with caution or you will have a hard time knowing which one to stop. At any rate,
KILL the_target_session_id;should be enough to stop the refresh. Do note the refresh ends in failure, but that’s the price to pay for stopping it manually — after all Power BI is unexpectedly getting its connection interrupted.
On another note, I found this method to be ineffective. I don’t fully grasp why some sessions were stopped with one kill command and others persisted, but this persistence led me down to the query level.
Solution: stop queries
Okay, you tried to kill the session but it didn’t work. Or you decided to do it the right way and want to kill queries, not sessions that you can not fully understand the purpose. The following query has you covered
SELECT * FROM sys.dm_pdw_exec_requests WHERE end_time IS NULLSimilar to sessions, the above searches for unfinished queries. Have a look at an example below.
In this instance I only had one query running, but I needed it gone because it was running at a bad time. Well, KILL also works with query ids
KILL your_query_id;Better yet, this sys table brings back the query id as request_id and also the respective session id as session_id.
Closing thoughts
Recently I had an experience where I just couldn’t have Power BI refreshes hitting my SQL pool but I had no way of stopping them. The API route was available sure, but I couldn’t use it at the time. So I went directly to the source and interrupted the connection between the two services.
Again, keep in mind this results in a refresh failure which you might not want your users to see, so use this method carefully. In the future I definitely want to experiment with cancelling the refresh via the API, but for now I have this SQL trick up my sleeve :)
On a last note, I’d like to raise awareness that this didn’t result first try every time. I already mentioned this at the session level, but even at the query level it also happened. On the latter I simply killed every subsequent query until the refresh finally stopped. This probably means the Power BI Service has some built-in fault tolerance to retry queries, so double check the sys table to ensure the query or session is definitely gone.
