This past weekend we added 2gb of memory to our server, bring it to 8gb. We are running Win2000 Advanced Server with SQL Server 2000 Enterprise Edition. Before the memory upgrade I had the SQL Server manually set to allocate 4gb out of the 6gb to SQL Server. After the upgrade I manually set SQL Server to allocate 6gb out of the 8gb to SQL Server. Now the fun stuff starts. Now jobs start failing, the master database is reported to have problems(but no DBCC command reports any errors) and other weird things. I manually set it back to 4gb and nothing changes, still weird behavior. I manually set it to dynamically take between 1gb and 6gb based on needs and everything starts to work fine. (so far) I have worked with Dell and have ran their memory diagnostics and they say everything is fine. The server is all Dell parts and all the memory came from Dell. What the #$%#^& is going on? Live to Throw Throw to Live
i would suggest you remove the /3GB from boot.ini then see if you can increase the SQL Server memory in increments to 6GB the problems you are having are probably actually address space, a 32-bit OS has 4GB address space, of which 2 or 1 GB is allocated to the OS, many programs report this as a memory issue, which is not correct, the /3GB switch reduces the address space allocated for the OS, the /PAE can also increase OS memory & address space usage, but not if you reduce the amount of AWE memory used by SQL anyways, the correct method for assessing whether SQL needs more physical memory is disk IO and procedure cache evictions, not out of memory messages, which actually indicates a shortage of address space
So if I have a Win2000 Advanced Server with 8gb of memory and I want to allocate 6gb of memory to SQL Server, then I don't want the /3GB switch in my boot.ini file? Live to Throw Throw to Live
not necessarily, if you have a well designed app, extensive use of stored procs, very few compiles, no use of cursors, other API Server cursors, extremely careful use of xprocs, these apps frequently run fine with both /3GB and /PAE apps designed by people who did not understand what was going on under the hood cab cause SQL to crash through some combination of /3GB and /PAE watch in Perfmon, the process object, the sqlservr instance, virtual bytes and private bytes counters, also the SQL Server errro logs, look for messages like: fail to allocate 65536 bytes or 131072
I got AWE enabled last night, told SQL server to take 5.5gb out of the 8gb, not a range of memory. I rebooted and let the nightly jobs run. There are some big jobs that run overnight, joining tables with millions of rows, etc. Everything ran fine with no errors last night!!! I will continue to monitor it, but it appears everything is working fine and SQL Server is using the memory. Thanks for the response(s) to my post. Live to Throw Throw to Live
