A very common requirement when administering an SSAS instance or cube is killing a particular connection, SPID or Session, this could be due to a lengthy operation exceeding the expected time to completing, or merely cancelling a transaction that was issued by mistake, hopefully that wont be a schema change on a live environment tho!.

This post goes through the XMLA required for killing an SSAS command, as well as the Analysis Service DMVs that can be utilized to identify the required IDs.

In order to cancel a command on an SSAS database, you need to issue an XMLA command on that database, this can be done by starting a new query on the database with type XMLA.

The XMLA for killing a command looks like this:

The XML layout above shows that you can cancel a command on different levels, below is a walk-through of each element:

• ConnectionID: Highest level to cancel a command at, this will cancel all SPIDs executed by this connection ID to the SSAS database.
• SessionID: A Session belongs to a Connection ID and can have many SPIDs executed within one Session.
• SPID: This is the specific Server Process ID that has been assigned to this Session.
• CancelAssociated: This is a True/False flag to indicate if the Cancel command is propagated to all commands associated with the supplied ID, if omitted “False” is assumed.

Below is an image that visualizes how the hierarchy of commands look in SSAS, this should clarify what level you need to cancel a particular command, and how each level connects to the one below.

If you are an administrator, then you can issue the SSAS Cancel command on any level shown above by supplying the ID for that particular connection, session or SPID. In order to identify these IDs, you could look into the following SSAS Dynamic Management Views:

This will return all active connections to a particular SSAS Instance Database, along with useful information such as the ConnectionID as well as user, time and data based statistics

This DMV will return all active sessions on a particular SSAS database, this DMV can be linked to the connections DMV using the SESSION_CONNECTION_ID column.

Currently executing commands, this can be linked to the Sessions DMV using the SPID column.

That should give you all you need to start killing those commands!

Resources:

• XMLA Cancel Command on MSDN

Kill a Session, SPID or Connection to Analysis Service Cube rated 5 out of 5 by 1 readers