Kill stuck SSAS Cube Processing

Recently, I came through an odd issue. My SQL Agent job which performs SSAS Cube processing was stuck for a while. Usually, it completes in few minutes, but something strange happened and it was stuck for more than hour.

After googling and trying to find the reasons behind this unusual behavior, I realized that most of advices recommended restarting Analysis Services on your server. I was quite disappointed that there is not something like “kill” in T-SQL, so I’ve decided to investigate further before restarting Analysis Services.

Then, I found out that in fact there is an option to kill the processing of the cube, using XMLA (this is a special language for accessing data in analytical systems, but it is out of the scope of this blog post).

First, we need to find ID of the connection which is causing problems. Open your instance of SSAS, select new query and type this in the query editor (it’s MDX syntax):

SELECT * FROM $SYSTEM.DISCOVER_CONNECTIONS
GO
SELECT * FROM $SYSTEM.DISCOVER_SESSIONS
GO
SELECT * FROM $SYSTEM.DISCOVER_COMMANDS
GO

Find the IDs of the process that is causing problems, copy and paste them into the following XMLA code:

<cancel  xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400">
  <connectionid>CONNECTION ID THAT NEEDS TO BE KILLED</connectionid>
  <sessionid>SESSION ID THAT NEEDS TO BE KILLED</sessionid>
  <spid>SESSION SPID THAT NEEDS TO BE KILLED</spid>
  <cancelassociated>true</cancelassociated>
</cancel>

Ant that’s it! You’ve just got rid of the stuck process.

Last Updated on February 5, 2020 by Nikola

Spread the music: