How to Kill Hung SSAS Queries

Sometime in your SSAS career you will hit a bad query that will proc your SSAS server. Restarting the service will resolve the issue temporarily but depending on how often that query hits the server, you might not have enough time to run a trace or diagnose the issue. So to keep the process down, you can run the following commands to find the bad query and kill it so you have more time to fix the actual problem.

1. In the cube that you're experience issues with, open up a MDX session and run the following query:
   select * from $system.discover_sessions
   where session_status = 1
2. That will give you a list of the sessions that are currently running active queries.
3. Open up a XMLA session in the cube you are working on and run the following command:
   <Cancel xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
   <SPID>insert the SPID from step 1 here</SPID>
   </Cancel>
4. Monitor the CPU drop in your SSAS box and enjoy!

SSAS File Corruption

This following error made me work on a Sunday night:

The following file is corrupted: Physical file: <blah>

Pretty much a server bit it pretty hard and after a manual reboot it came back up with a bunch of cubes having these corruption issues.

Sadly at this point all you can do is pray you have a decently recent backup to restore from however just working with the SSAS database is hard enough; if you try to do anything (detach, delete) it will just throw the corruption error.

Only way to handle this is to:

1. Shutdown the SSAS service instance
2. Find where the cube data directory is stored and delete all files related to the corrupted cubes
3. Startup the SSAS service instance
4. Restore from backup
5. Pray

Hope you don't have to deal with this issue like I have.