Disconnect Users and Sessions on Analysis Services Server
An administrator of Analysis Services may want to end user activity as part of workload management. You do this by canceling sessions and connections. Sessions can be formed automatically when a query is run (implicit), or named at the time of creation by the administrator (explicit). Connections are open conduits over which queries can be run. Both sessions and connections can be ended while they are active. For example, an administrator may want to end processing for a session if the processing is taking too long or if some doubt has arisen as to whether the command being executed was written correctly.
Ending Sessions and Connections
To manage sessions and connections, you can use Dynamic Management Views (DMVs) and XMLA:
In SQL Server Management Studio, connect to an Analysis Services instance.
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
Press F5 to execute the query.
In contrast with XMLA that returns query results as XML, running a DMV query returns session and connection information in a tabular result set that is easier read and copy from.
To end a session, open an XMLA query window. Paste the following syntax into an MDX query window, replacing the SPID placeholder with a valid session SPID that you copied from the previous step.
<Cancel xmlns="https://schemas.microsoft.com/analysisservices/2003/engine"> <SPID>111111</SPID> <CancelAssociated>1</CancelAssociated> </Cancel>Press F5 to execute the cancel command.
Ending a session stops all commands that are running as part of that session. Ending a connection closes its host session. However, Analysis Services is not able to close a connection if it cannot track all the sessions that are open by it, for example, when multiple sessions are opened in an HTTP scenario.
For more information about the XMLA EXECUTE METHOD, see Execute Method (XMLA).