Forum Discussion

rks's avatar
rks
Resolver II
2 years ago
Solved

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

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

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

4 Replies