Overview: Quick tip for cancelling a long-running SQL Server Analysis Services query. Screen shots & statements are from SQL Server 2008 R2.

We’ve all been there…waiting in BIDS for a report to render. You begin to think uh-oh. After a bit longer you know it’s uh-oh.

There’s multiple ways to accomplish cancelling the session; here’s one way to cancel a long-running SSAS query. One caveat: this technique does require elevated privileges.

Step 1: Use a DMV to find the SPID

First, you need to find the SPID associated to your long-running report query. We’ll use a Dynamic Management View (DMV) for that. First let’s launch an MDX window in SQL Server Management Studio:

Obviously you can modify the select statement here to whatever columns you wish to see.

Query:

SELECT

session_spid

, session_user_name

, session_last_command

, session_current_database

, session_cpu_time_ms

, session_elapsed_time_ms

, session_start_time

, session_last_command_start_time

, session_last_command_end_time

FROM $system.discover_sessions

WHERE session_status = 1

AND session_user_name = 'DomainName\MCoates'

ORDER BY session_start_time desc

Results:

In the above query, I restricted the results to just my user name. Using the results, find the query associated to your long-running report. Note the Session_Spid (i.e., the Server Process ID).