The problem
I have Analysis Services (AS) 2000 with SP3a installed on my SQL Server. My organization has been working with it for the last few months with no problems. When I returned to work after a holiday weekend, I discovered that I cannot connect to it anymore.
My colleagues were sure that "the server was not touched."
It all started when I tried to register the Analysis Services server from another machine. I got the following error message:
Connection to Server Failed
Error occured while connecting to MyServer
Cannnot open connection to Analysis server 'MyServer'.
Timeout occured.
Do you still want to register this server?
[IMAGE]
I connected to the machine where the AS instance is installed (MyServer) and saw that the AS service was not started. I started it and was then able to connect.
Surprisingly, after a few minutes, I was unable to connect to Analysis Services anymore.
I looked at the service again and it was not started. I started it and could connect, but after only a few minutes it stopped again.
Possible solutions
What is the first thing an IT person does when there is a new problem on an existing server? Restart the machine, of course. That didn't solve the problem.
I suspected a permissions issue, so I modified the account that the service runs to my domain account, which is a domain admin. That didn't solve the problem.
At that point, my guess was that this is a configuration matter. Since I have another SQL Server Analysis Services 2000 instance running on another machine, I could compare my instance to it. So I started the problematic AS service and was able to connect fast. I right clicked the two instances from the AS manager → Properties and compared the settings tab by tab. That was a good idea because I found the prob
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
lem!
Solving the problem
When I clicked on the Environment Tab, it looked like this:
[IMAGE]
When I tried to move to the next tab without changing it, I got the following error:
[IMAGE]
So I modified the Memory conservation threshold setting to 1536 (see explanation):
[IMAGE]
It worked! I restarted the service and the instance didn't stop anymore.
Explanation
While investigating the problem and solution, I found the article Microsoft SQL Server 2000 Analysis Services Operations Guide. In the "High and Low Memory Settings" section it says:
"The default value for the Memory conservation threshold setting is the amount of physical memory on the computer at the time of installation. The default value for the Minimum allocated memory setting is half the amount of physical memory on the computer at the time of installation. If you change the amount of memory on the computer after installation, you must manually modify these values. Otherwise, Analysis Services will not properly utilize the actual amount of physical memory on the computer."
Hence, except for discovering that one of my colleagues has a very short memory span (he DID add memory to that server after all…), I also learned that when you modify the amount of memory on the machine, it automatically alters the Memory conservation threshold setting to zero.
Final configuration
Since my server has 4 GB of RAM in total now, I configured the conservation threshold to be around 3.5 GB and I gave half of it (around 1.8) for the minimum allocated memory. I also added the /3GB switch to the boot.ini as described in the link above, at the end of the "High and Low Memory Settings" section.
[TABLE]
