Analysis Services running out of memory periodically

We are running SQL Server 2000 (with SP3) on Windows 2000 Advanced
Server. The machine is a Dell Optiplex with Intel Xeon at 1.6GHz. The
server has 4GB memory.

We have 2 main OLAP databases that we refresh at the end of the ETL
run. It is an automated process that gets fired through an external
scheduler.

About a year back, we used to see 'out of memory' errors about once in
a week. Of late though, these errors have become much more frequent and
it has come to a point where the databases cannot be refreshed twice in
a row.

Could there be a memory leak in AS 2000? Is there a way to troubleshoot
that?

Just fyi I have read some of the related posts, and I should mention
that in both the databases, there is at least one dimension which is
about 100K rows. The fact table itself in each of the databases is
about 1 million+ rows.

Any help in troubleshooting this problem will be greatly appreciated.

We had similar problems recently and they've now been resolved.

Firstly, AS loads all dimensions from all AS databases into memory when the services starts. In our case, we had 10+ databases and there were a lot of redundant dimensions, where we'd "archived" and "restored" the databases to provide the users with snapshots at a given point in time. We've now removed all redundant dimensions. Also, we've managed to be able to save these archive databases, so that they can be restored later.

Secondly, AS can only access 2Gb of this memory by default. To enable AS to use more (only up to 3Gb), you can add a /3Gb switch to the boot.ini file (see microsoft tech for full details). This enables AS to use more than 2 Gb, but you should also amend the high memory limit value (either within AS or the registry - again check microsoft for details). The high memory limit is recommended to be no higher than 2.7 Gb as some memory is needed for a "cleaner thread".

We've got the /3Gb switch set and the high memory limit set at approximately 2.5Gb. You might need to be a bit careful if you change your values as you've only got 4 for the whole server and SQL will need a fair amount (we have got 6 Gb of memory).

Another point is that we were running with 4Gb for a while until we realised that we actually had 6 installed although windows told us we only had 4. This was resolved by adding another switch to the boot.ini file - /PAE. For some reason windows would only recognise 4 Gb without this switch.

The AS process was running at 1.7Gb previously, now we're down to 0.6 Gb and we've had no more memory problems.

Hope this helps with your problem.