SQL Server 2000 2GB memory limit?

[Brian]

SQL Server 2000 SP3 on a Dell dual 2.4GHz Xeon box 3GB RAM Windows 2K
SP4. Two aplication dbs, each less than 2GB in size.

Had a problem where we would run Solomon queries and what not against
the box. It had 2GB RAM, and sqlserv.exe would take up to 1.85GB of
RAM, exhausting the physical RAM on the box. SQL would choke and the
Solomon users would have problems, and I would have to restart the SQL
service.

I added another GB of RAM, bringing the box to 3GB, and increased the
paging file. The OS sees it, and SQL sees it. I check Enterprise
Manager, and tell SQL to dynamically configure memory, and it offers an
upper limit of 3071MB, so it "sees" the 3GB.

I can stress the box with queries to the point that sqlserv.exe takes
1.99GB of memory (as viewed through Task Manager) and then SQL server
chokes. It never goes past 2GB, and the OS and box continue running
fine.

Does SQL server 2000 have some upper limit, or do I just need to change
some setting through EM?

Thanks.

[Mike Epprecht \(SQL MVP\)]

Hi

Standard Edition of SQL Server 2000 has a limit of 2GB or RAM. Enterprise
Edition can use more.

INF: SQL Server Memory Usage
http://support.microsoft.com/default...b;en-us;321363

--
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mi**@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"Brian" <br****@clarkreid.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

SQL Server 2000 SP3 on a Dell dual 2.4GHz Xeon box 3GB RAM Windows 2K
SP4. Two aplication dbs, each less than 2GB in size.

Had a problem where we would run Solomon queries and what not against
the box. It had 2GB RAM, and sqlserv.exe would take up to 1.85GB of
RAM, exhausting the physical RAM on the box. SQL would choke and the
Solomon users would have problems, and I would have to restart the SQL
service.

I added another GB of RAM, bringing the box to 3GB, and increased the
paging file. The OS sees it, and SQL sees it. I check Enterprise
Manager, and tell SQL to dynamically configure memory, and it offers an
upper limit of 3071MB, so it "sees" the 3GB.

I can stress the box with queries to the point that sqlserv.exe takes
1.99GB of memory (as viewed through Task Manager) and then SQL server
chokes. It never goes past 2GB, and the OS and box continue running
fine.

Does SQL server 2000 have some upper limit, or do I just need to change
some setting through EM?

Thanks.

[Erland Sommarskog]

Brian (br****@clarkreid.com) writes:

SQL Server 2000 SP3 on a Dell dual 2.4GHz Xeon box 3GB RAM Windows 2K
SP4. Two aplication dbs, each less than 2GB in size.

Had a problem where we would run Solomon queries and what not against
the box. It had 2GB RAM, and sqlserv.exe would take up to 1.85GB of
RAM, exhausting the physical RAM on the box. SQL would choke and the
Solomon users would have problems, and I would have to restart the SQL
service.

I added another GB of RAM, bringing the box to 3GB, and increased the
paging file. The OS sees it, and SQL sees it. I check Enterprise
Manager, and tell SQL to dynamically configure memory, and it offers an
upper limit of 3071MB, so it "sees" the 3GB.

I can stress the box with queries to the point that sqlserv.exe takes
1.99GB of memory (as viewed through Task Manager) and then SQL server
chokes. It never goes past 2GB, and the OS and box continue running
fine.
What do you mean with choke? Even if SQL Server only is able to use
2GB, you should have no problem to run 2GB databases with 2GB of
memory, unless they are extremely poorly designed. And not even then
should SQL Server "choke".

Is there something in the SQL Server errorlog when the server "chokes"?
Does SQL server 2000 have some upper limit, or do I just need to change
some setting through EM?

I will have to admit that I'm not too well versed on the issues about
large memory. Brian Moran had an excellent article about this in
in the March issue of SQL Server Magazine,
http://www.windowsitpro.com/SQLServe...155/45155.html.
That's a subscriber-only article, but already the visible intro might give
you some hints.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp