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!

SSIS Catalog Validation Anonymous Logon Bug

For some reason when I changed the connection string parameters on my SSIS Catalog project I started getting a weird bug where the project fails connecting to the SAME server via a different with login failed error code. After some digging it looks like the project is trying to connect to the new IP via 'NT Authority\ANONYMOUS LOGIN' credentials instead of using the SQL Agent credential in the agent job. If I revert the connection string parameter back to the original IP (same server) there is no issues.

Well apparently this is a known bug:
https://connect.microsoft.com/SQLServer/feedback/details/727219/login-failure-message-during-ssis-2012-package-validation

And sadly it has not been fixed even with SQL 2012 SP1. Currently the easiest work around is scripting out the SSIS catalog job call and have EXECUTE AS LOGIN = 'domain\db service account' execute prior to the rest of the script.

Stupid issue but at least there is a work around. Hope this information can help someone else who is experiencing the same issues.

Windows Server 2012 crashes after enabling Hyper-V

A little off my usual topic but this was a super annoying problem I had to deal with... I enabled the Hyper-V role in Server 2012 and my machine no longer goes past the Windows logo. Sadly there is no Windows Restore feature in Server 2012 not to mention I wasn't able to uninstall Hyper-V in safe mode so I was essentially stuck.

Solution? Disable USB 3.0 controller in BIOS. BAM! Things work again. Apparently Hyper-V and USB 3.0 don't like each other. Thanks Microsoft for wasting an hour of my time.

Datetime Parameter Mapping issue with SSIS

This might be common knowledge but I spent a good 30 minutes trying to figure out why my Execute SQL task wasn't inserting a datetime variable value in to my database. Generally when working with SQL I know that the datetime data type can convert to DBTimestamp with no issue but for some reason it keeps failing whenever I set the data type in the parameter mapping settings of the SQL task to DBTimestamp... Setting it to varchar or long would make the task succeed however it would have inserted a wrong date and time.

The fix? Simple.... set the data type to Date instead of DBTimestamp! DOH!

/cry

Why SQL 2012 SSIS Catalogs are useless

When I first got started playing with the new SQL 2012 SSIS Catalogs I was pretty excited; To be able to use project parameters instead of config files and using the new project deployment model instead of the package deployment model makes life alot easier in the world of managing and deploying SSIS packages.

However there has been a few issues with the new features that has been a pain to work with:

1. Project hangs indefinitely due to the new SSISDB logging
   Packages that use to run in SQL 2008R2 now hangs with SQL 2012. This may be caused by deadlocks with the SSISDB database. Turning off logging in the job resolves this issue however when I need to troubleshoot anything I have to manually turn it back on and off again.
2. SSIS Server Maintenance Job breaks jobs and causes locks
   This is a default job created when installing SSIS 2012. It cleans out all old history and project revisions as configured. However when this job is running it will cause all other jobs to fail as it is placing a lot of locks on the SSISDB table. The job itself can take 3-4 hours to complete which means our other jobs are failing in the meantime causing massive backlogs.
3. SSISDB logging and reports are tediously slow to use
   It's nice to see that Microsoft decided to create better reporting features with SQL 2012 however it is way too slow to use and navigating it to find issue is painful. Not only do I have to deal with the slow response time, but navigating between pages and finding issues is hard to figure out.

In summary we have decided not to use SQL 2012's SSIS catalog till SP1 hits, hopefully all the issues I listed above will be resolved then.

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.

Service account doesn't have rights to check domain user SIDs

We started getting the following error in SSRS on subscriptions executions

Failure sending mail: The report server has encountered a configuration error. Mail will not be resent.

I naturally started looking through the SSRS logs and found this wonderful gem:

ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The Report Server has encountered a configuration error; more details in the log files, AuthzInitializeContextFromSid: Win32 error: 5; possible reason - service account doesn't have rights to check domain user SIDs.;

So what am I to do? Well the good news is there is a KB article that helped point me in the general direction of what to look at: http://support.microsoft.com/kb/842423.

I created a test subscription (as advised from the KB article) that would execute within a minute which only created a link to the report not embedding or attaching the report. After successfully receiving this report it confirms that the AuthzInitializeContextFromSid API function call is the issue and the service account is having permission issues executing it.

The three methods to resolve this issue in the KB article didn't apply to me as I know that the SSRS service account I use has enough permission to run everything required as it was working before. So instead of following the directions I decided to restart the SSRS service instead.... and then... BAM! "Service cannot restart due to a LogOn failure."

At this point I re-entered the service account credentials, started up the service, sent a test subscription and everything works again. So super weird how Server 2008R2 forgot the service credentials but at least it was a simple fix.