XMLA Command for Cancel SPID not working (session_status = 3)

[rks]

Hi Community,

we have refreshed a dataset, but something happened. We don't know exactly what. The SQL profiler is silent, and there's no active session on the database either. But somehow, there's an active session that blocks new refreshes. Since we want to refresh our datasets every hour, we are completely stuck. There are several tutorials on how to cancel a refresh session - like these ones: 

* Disconnect Users and Sessions on Analysis Services Server | Microsoft Learn

* Cancelling a Power BI Premium Dataset Refresh (elegantbi.com)

So we cancelled a session without using <CancelAssociated>1</CancelAssociated>

And yay! - it worked: 

Executing the query ...
Run complete

However, the connection apparently stays open and we cannot start a new refresh. 

The session (or connection?) is still blocking new refreshes, until 5 hours later, the connection expires and new refreshes are possible again. From our research, session status = 3 means that the session is killed. But it's still there!

MSFT support claims there's nothing they can do. We will have to wait until something magical happens 5 hours later and new refreshes are possible again. 

My question to the community: 
Are there ways to start a new XMLA refresh even though one connection is being blocked/locked/not properly killed?

BTW: This error occurs since 2 weeks nearly once per day on different datasets, the 1,5 years before it never occured. Hmmmmm

[SaiTejaTalasila]

Hi @rks ,

You can use rest API commands.

https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/cancel-refresh

Thanks,

Sai Teja 

[lbendlin]

If you are really desperate then move the workspace off Premium, and then back on.  That sometimes helps to unstick it.

[rks]

Hm, good idea @lbendlin, it seems like a restart, which always worked with Analysis Service back in the days.

The datasets are quite large, would data loss occur in these scenarios?

[lbendlin · Accepted solution]

Your current semantic model contents will be preserved but the potential changes from the refresh will be discarded.