SQL 2000 server only using half a gig

yoda · 29 May 2008 at 15:26

I got an SQL server, it's database is 14.5GB and the server refuses to use more than 512MB for SQL service dispite having 2GB of free ram...

I try to alter the min and max ram settings from the SQL manager but it says in the log 'can not allocate 1024MB'

I've got "/PAE /3GB" in the boot.ini and the server is running 4GB ram...

Anyone know why it not letting me?

thanks

yoda · 30 May 2008 at 13:17

hmmm.... been reading....
I'm wondering if SQL server 2000 is using kernel space not user space....
if I enable /3GB option... user space is the 3gb and kernel space is 1gb as apposed to 2GB / 2GB

I'm going to have a play this weekend....

Any more ideas?

thanks

yoda · 2 Jun 2008 at 09:06

ok, I've removed the /3GB option from boot.ini and rebooted
SQL server is now using 1,061,496K so, thats about 1GB...
The server will get used today, I'll see if I can hammer it to see if it will use more...

Might see if we can put more ram in server...

anyone got any thaughts ect?

Thanks

:edit
The server is still reporting 2GB of ram left?????????

madman045 · 2 Jun 2008 at 10:12

SQL 2000 will generally use memory dynamically, and depending on the version, will use upto a maximium of 2GB

I have a client who has 45 users on a server that's their SQL, Application and TS box and SQL 2000 uses 1.8GB ram, the DB isnt huge, but it's heavily used.

yoda · 2 Jun 2008 at 12:36

well, our DB is 15GB, sql server is now using 1068468K and seems to be running a darn sight faster....

Madman045, what settings you got?

madman045 · 2 Jun 2008 at 12:59

Taken from the memory tab in enterprise manager

Currently using 1.8GB memory

yoda · 3 Jun 2008 at 14:34

madman045 · 3 Jun 2008 at 19:47

Do you actually need to reserve anything? if you can try the same settings as my clients, once changed, restart the SQL services, also what service pack level is yours up to and what version of SQL 2000 do you have?

yoda · 5 Jun 2008 at 10:13

they are on...
SBS 2003 SP2

I'm not sure what SP the SQL2000 is on... how do I find out?

yoda · 5 Jun 2008 at 10:46

I just phoned them... I gave them a quote for under £200 to double their servers memory from 4GB to 8GB... they said yes and they didn't even argue... I think they learned not to say no otherwise they get ear-ach... lol

#Chri5# · 5 Jun 2008 at 12:06

yoda said:
I just phoned them... I gave them a quote for under £200 to double their servers memory from 4GB to 8GB... they said yes and they didn't even argue... I think they learned not to say no otherwise they get ear-ach... lol

SBS 2003 won't support more than 4GB as it's Server 2003 Standard (IIRC).

yoda · 5 Jun 2008 at 14:40

oh, joy...

oh well... looks like we'll have some un-used ram then...
i've been told it's SBS2003 prem and SQL 2000 prem....
not sure what the diference is... lol

madman045 · 5 Jun 2008 at 21:54

Now it makes sense, as your running on SBS, i doubt it will allocate anymore memory to it as its also the domain controller and exchange server and exchange will also dynamically assign memory and possible the reason why it wont allocate anymore.

Either upgrading to SBS 2003 R2 Premium which will give you SQL 2005 Workgroup edition or if the SQL DB is so important, how about a machine just with SQL on it?

#Chri5# · 6 Jun 2008 at 11:17

Or hang on a bit for SBS 2008. The Premium version of 2008 is now a multi-server product - MS expect you to put SQL on a seperate physical box and is hence licensed for that.

madman045 · 6 Jun 2008 at 16:27

I have SBS 2008 RC0 installed at present, it's a massive change, as is the hardware requirements, Primary server, 64bit only, 2GB ram needed to install, recommended minimal 4GB (up to 32GB), 60GB install and then the second server (32bit/64bit) for SQL

So if you just need something for a database, then perhaps just a 2003 server with SQL 2005 Workgroup edition, if you dont have loads of users, then CALS will be fine, if its 50+ users, then maybe a single CPU license.