At a client we had to produce a server, in order for us to have a development environment, since they couldn’t spare the server power. Odd situation, but that’s not the point. The point is that, somewhere during the installation process of this server, after installing SQL Server, we noticed that the server wasn’t a domain controller. Now the SQL Server and in particullar Reporting Services (SSRS) was installed with credentials granted to the build-in Network Services account. This account gets a new SID once we installed the domain controller, and after that SSRS started acting on us.
The new Network Services account needs to be granted modify permissions to the following folders:
One can only guess, but it seems as if there was a memory leak in some method in the mdsonar.cpp file used by Analysis Services.
As I was trying to create a cache for a cube, to speed up querying, I ran into the error message listed below:
When I binged the filename mdsonar.cpp, I ony got two (2) hits. Normally this would freak me out a little, but the two links were actually the ones I was looking for, sort of. At least they lead to a plausible solution, unfortunately not an explanation. The solution is to apply the latest Service Pack/Cumulative Update, as listed in KB2660492, for the SQL Server 2008 (not R2) installation I was working on. Regrettably I am not in a position to verify this KB will actually solve this problem, as I am on a tight schedule and out of time. This is really one of those situations where the client would benefit from a little extra effort.
In short I was trying to create a cache of a fairly large set, comprised of several attributes from a couple of dimensions. One dimension has about 850K members and the other has about 45K member. It would seem this was too big a mouthfull, before Service Pack 3 on SQL Server 2008 Analysis Services.