Kill a Connection, Session, or Command in Analysis Services

One of the useful features of the SSAS Activity Monitor is the ability to cancel sessions and queries. Though there is no obvious way to do this through SSMS you can in fact write an xmla query to kill either the session, connection or command:

If you specify the connection ID it will kill all sessions, if you kill a session it will cancel all SPIDs that pertain to that session, and if you kill the SPID it will kill that one command. Cancel associated will kill all subsequent commands form the connection/session/SPID. You do need to be an administrator to run cancel commands.

By using MDX you can query the DMV’s to find the appropriate ID you wish to cancel:

Within  SSAS Activity Monitor the same MDX DMV’s are used, but we use the query below for active sessions, dormant sessions, and blocked sessions.

Use these queries to help find the sessions that may be causing you issues.

I’ve pretty much moved on from developing on SSAS Activity Monitor but recently there were some changes made to it by a new contributor, and the managing of the project is done pretty much by Marco Rusoo, but it’s still a good tool.

Leave a Reply

Your email address will not be published. Required fields are marked *