How to kill or disconnect session or users on SSAS server

There are multiple ways to do this. We will explore using hybrid approach as mentioned in this Microsoft article.

  1. In SQL Server Management Studio, connect to an Analysis Services instance.
  2. Paste any one of the following DMV queries in an MDX query window to get a list of all sessions, connections, and commands that are currently executing:

    Select * from $System.Discover_Sessions

    Select * from $System.Discover_Connections

    Select * from $System.Discover_Commands

  3. Press F5 to execute the query.

    The DMV query returns session and connection information in a tabular result set that is easier read and copy from.

    Keep the query window open. In the next step, you will want to return to this page to copy the SPIDs of the session you want to disconnect.

    To end a session, open a second XMLA query window.

  4. Paste the following syntax into an MDX query window, replacing the ConnectionID, SessionID, or SPID placeholder with a valid value copied from the previous step.

<Cancel xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”&gt;
<SPID>595618</SPID>
<CancelAssociated>1</CancelAssociated>
</Cancel>

or

<Cancel xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”><ConnectionID>111</ConnectionID&gt;
<SessionID>222</SessionID>
<SPID>333</SPID>

<CancelAssociated>1</CancelAssociated>
</Cancel>

Thus you can terminate the long running queries or memory hogging sessions.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s