Problem connecting to SQL Server 2000 Analysis Services?

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 problem!

Solving the problem

When I clicked on t...

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL Server Business Intelligence (BI) and Data Warehousing Using the Pivot transformation in SQL Server Integration Services DBA career paths could lead to business intelligence Are data warehouses made for the cloud? Q&A: Business intelligence gets a facelift in SQL Server 2008 R2 Project Gemini gets a new name, Madison earns buzz Speed up reports in SQL Server Reporting Services with caching Data Transformation Services vs. SSIS: The key differences Using package configurations in SQL Server Integration Services (SSIS) How SQL Server 2008 components impact SharePoint implementations Achieving high availability and disaster recovery with SharePoint databases Microsoft SQL Server Performance Monitoring and Tuning Using traces in SQL Server Profiler SQL Server Mailbag: CALs, witnesses and unwanted changes SQL Server Mailbag: Data restoration and DB property management Working with IntelliSense in SQL Server 2008 Management Studio SQL Server Mailbag: Stored procedures, triggers and SSRS reports Troubleshooting Distributed Transaction Coordinator errors in SQL Server Clearing the Windows page file and its effect on server performance Optimizing SQL Server indexes –- even when they're not your indexes Performance implications of transaction log autogrowth in SQL Server The short course on how SQL Server really works Data Warehousing and Business Intelligence Recommended practices for SQL Server Analysis Services aggregations Creating and managing SQL Server Analysis Services partitions Sharing SSAS cube data in SharePoint with PerformancePoint Server 2007 New data profiling tools in SQL Server 2008 Utilize SSAS for data predictions and classification using Excel SQL Server 2008 Integration Services delivers new features Parent-child dimensions in SQL Server 2005 with Analysis Services MDX Change data capture in SQL Server 2008 improves BI reporting accuracy Manage traces in SQL Server 2005 Analysis Services with XMLA commands Tutorial: SQL Server 2005 Analysis Services

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary data aggregation  (SearchSQLServer.com) data preprocessing  (SearchSQLServer.com) data warehouse  (SearchSQLServer.com) FileMaker  (SearchSQLServer.com) GIS  (SearchSQLServer.com) MOLAP  (SearchSQLServer.com) pivot table  (SearchSQLServer.com) Quiz: SQL Server 2000  (SearchSQLServer.com) SQL  (SearchSQLServer.com) T-SQL  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

he 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.

ABOUT THE AUTHOR:   

Michelle Gutzait works as a senior database consultant for ITERGY International Inc., an IT consulting firm specializing in the design, implementation, security and support of Microsoft products in the enterprise. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. For the last 10 years, she has worked exclusively with SQL Server. Her skills include SQL Server infrastructure design, database design, performance tuning, security, high availability, VLDBs, replication, T-SQL/packages coding, and more.
Copyright 2007 TechTarget

Rate this Tip To rate tips, you must be a member of SearchSQLServer.com. Register now to start rating these tips. Log in if you are already a member. DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.