SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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:

View the code on Gist.

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:

View the code on Gist.

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.

Richie Lee's SQL Stuff

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.


Leave a comment on the original post [bzzzt.io, opens in a new window]

Loading comments...