Using DMVs and XMLA commands to force a disconnect from an Analysis Server

At one time or other, just about every DBA charged with administering a Microsoft SQL Server Analysis Services server has encountered a situation that necessitated forcing a disconnect from either a Multidimensional or Tabular instance of Analysis Services. In the relational world of SQL Server, that's relatively easily accomplished by using SP_WHO, to determine the SPID to be terminated followed by execution of a KILL command. Accomplishing the same thing with Analysis Services is a bit more complicated, but can be easily accomplished given a little familiarity with the Dynamic Management Views (DMVs) and XMLA commands. .

When a user connects to an Analysis Server, two objects are created on the server and both are easily detected using DMV queries. One of those objects is a Connection and the connections can be discovered using the following DMV query:

SELECT CONNECTION_ID, CONNECTION_USER_NAME, CONNECTION_IMPERSONATED_USER_NAME,

CONNECTION_HOST_APPLICATION, CONNECTION_LAST_COMMAND_ELAPSED_TIME_MS, CONNECTION_IDLE_TIME_MS

FROM $SYSTEM.DISCOVER_CONNECTIONS

That give us some information, but not quite enough yet to terminate a connection, because we need to get the SPID that is associated with the connection. The SPID is actually contained within the Session that is associated with the connection, and we can get that with the following DMV query.

SELECT SESSION_SPID, SESSION_CONNECTION_ID, SESSION_USER_NAME, SESSION_LAST_COMMAND, SESSION_LAST_COMMAND_ELAPSED_TIME_MS

FROM $SYSTEM.DISCOVER_SESSIONS

The SESSION_SPID column gives us the SPID associated with each connection and the SESSION_CONNECTION_ID maps directly to the CONNECTION_ID that was returned from the query against the $SYSTEM.DISCOVER_CONNECTIONS DMV. We definitely don't want to terminate SPID 1652, which is being used to run the DMV queries, but we do have a list of SPIDS from which we can select. We can tell from the output that SPID 3928 is currently idle and we want to terminate the connection because we know that the user has gone for the day and will not be returning for a couple of days. To do that, and this happens to be executing against a Tabular server, we'll execute the following XMLA command:

<Cancelxmlns=https://schemas.microsoft.com/analysisservices/2003/engine>

<SPID>3928</SPID>

<CancelAssociated>1</CancelAssociated>

</Cancel>

If we re-execute the DISCOVER_CONNECTIONS and DISCOVER_SESSIONS DMV queries, we'll find that connection 192 has been closed.