@#$%^&* memory problems!! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

@#$%^&* memory problems!!

Author

Topic

ws5926
Yak Posting Veteran

88 Posts

Posted - 2006-01-30 : 07:34:48

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

ws5926
Yak Posting Veteran

88 Posts

Posted - 2006-01-30 : 10:02:54

/3gb and /pae are in the boot.ini file. They have been there for about a year.

Live to Throw
Throw to Live

MichaelP
Jedi Yak

2489 Posts

Posted - 2006-01-30 : 10:29:31

What is the output of this command?
sp_configure

Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

ws5926
Yak Posting Veteran

88 Posts

Posted - 2006-01-30 : 10:38:25

name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
affinity mask -2147483648 2147483647 0 0
allow updates 0 1 0 0
awe enabled 0 1 0 0
c2 audit mode 0 1 0 0
cost threshold for parallelism 0 32767 5 5
Cross DB Ownership Chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
default full-text language 0 2147483647 1033 1033
default language 0 9999 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 2147483647 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 6144 6144
max text repl size (B) 0 2147483647 65536 65536
max worker threads 32 32767 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 1911 1911
nested triggers 0 1 1 1
network packet size (B) 512 32767 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 1 1
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
scan for startup procs 0 1 0 0
set working set size 0 1 1 1
show advanced options 0 1 1 1
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user options 0 32767 0 0

Am I to assume that AWE is NOT enabled for my server?

Live to Throw
Throw to Live

ws5926
Yak Posting Veteran

88 Posts

Posted - 2006-01-30 : 10:39:53

Sorry about those two big text posts, I was trying to post the table as a smaller text size. I thought that Size 6 meant 6pts.

Live to Throw
Throw to Live

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-01-30 : 11:42:26

AWE is NOT enabled on that server.


sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE
GO

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2006-01-30 : 11:55:15

Do you have SQL Server SP4 installed by chance? SP4 had some problems with AWE, but there is a fix available.

ws5926
Yak Posting Veteran

88 Posts

Posted - 2006-01-30 : 12:09:00

I will enable AWE tonight. Yes, we have SP4 installed. Where do I find the fix? I think I may have installed it, this sounds familiar.

Live to Throw
Throw to Live

MichaelP
Jedi Yak

2489 Posts

Posted - 2006-01-30 : 13:19:02

I think Rick is putting you on the correct path here. Well done!

Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

ws5926
Yak Posting Veteran

88 Posts

Posted - 2006-01-31 : 10:01:19

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

Kristen
Test

22859 Posts

Posted - 2006-01-31 : 10:16:35

"I thought that Size 6 meant 6pts"

You should be able to use the DELETE icon on those two posts (as you are the author) to get rid of them - if you so wish!

Kristen

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-01-31 : 11:30:42

quote:
Originally posted by MichaelP

I think Rick is putting you on the correct path here. Well done!

Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

Do you know, that sounds really condescending..

I knew because i've come across the problem before, in fact I got one days consultancy fee due to just this problem..

ws5926
Yak Posting Veteran

88 Posts

Posted - 2006-01-31 : 11:47:47

I didn't take it as condescending. I manage SQL Server installations and I have a hard time sometimes keeping track of what tweaks I have done to each server. I thought that I had already solved this problem on this server, but it must have been another one. I took no offense.

Unless of course, you were talking about the <Yoda> tag at the bottom. That was very offensive and I am forever scarred by his reference to the Dark Side. I will never turn! Ha. ha.

Anyway, no offense taken. It didn't even enter my head till someone posted about it.

Live to Throw
Throw to Live

MichaelP
Jedi Yak

2489 Posts

Posted - 2006-01-31 : 11:53:02

Rick, that's part of my sig man. That sig is due to the fact that soooo many people never use the search on the forums or on the main site. This particular problem probably could have been found with a forum search, but not easily.

On the other hand I was trying to compliment your post because my post plus your post put this guy on the right path.

Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-01-31 : 12:02:35

think Rick is putting you on the correct path here. Well done!

I meant that bit, and towards me. Was having a bit off a laugh, but never mind, my twisted English humour doesn't suit many people..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources