SQL Refreshes Service and does not capture all allocated memory

Question

SQL Refreshes Service and does not capture all allocated memory

Clovis Celestin

SSC Enthusiast

Points: 141
More actions
August 26, 2005 at 12:17 am

#87336

Environment: Active/Passive Cluster, 8GB RAM, (6GB dedicated to SQL through AWE memory and boot.ini with both switches /3GB /PAE), 8 CPU, 1TB Drive Space
I had a scenario where SPID3 reported the SQL Service being restarted after error 18733 and through my research, we have identified that this is indeed normal behavior when the SQL internal scheduler cannot acquire resources needed, in our case disk.
The problem was tracked back to Norton Anti-Virus scanning the SQL drives during high transactional, peak time.
The real problem came when the service was refreshed. Sql only acquired 2.7 GB.
Has anyone seen this behavior?
Any explanations?

crasc

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

gbn SSCertifiable Points: 6429 More actions · Answer 1

http://support.microsoft.com/default.aspx?scid=kb;EN-US;316749 explains that SQL will use all but 384MB of it's memory between 2 and 3GB.

I guess here that AWE and PAE isn't being recognised, and that SQL is only using 3GB less 384MB (or 2.7Gb).

Anything in the Windows or SQL event logs?

Have you SQL SP4 and need to apply the AWE fix?

Cheers

Clovis Celestin SSC Enthusiast Points: 141 More actions · Answer 2

Nothing in the Windows log.

The SQL log has the following entry:

2005-08-20 22:12:59.97 spid1 Warning: unable to allocate 'min server memory' of 6147MB.

Thanks

crasc

gbn SSCertifiable Points: 6429 More actions · Answer 3

What service pack for SQL? What hotfixes? What OS and SP?

SQL cannot use min memroy because it cannot see it. Why can it not see it?

Clovis Celestin SSC Enthusiast Points: 141 More actions · Answer 4

Currently, Windows 2K SP4 and SQL 2K Ent - SP3a_997 (997 hotfix was added to resolve High Query Execution Plan management)

crasc

gbn SSCertifiable Points: 6429 More actions · Answer 5

Sounds OK.

When you say "service was refreshed" do you mean restarted?

I'd check the boot.ini on both nodes and reboot each node in turn.

I'm guessing that the server has "forgotten" about the PAE memory for some reason and it's not a SQL issue because the OS is not "presenting" the memory to SQL to use

Clovis Celestin SSC Enthusiast Points: 141 More actions · Answer 6

The server experienced a scheduler problem with error 17833 and initiate a service restart. Per our configuration in the cluster admin, SQL will attempt to restart 3 times prior to failing over. During the refresh, the sql service came back online but with only 2.7 GB of ram. It never failed over to the other node. My logs through foglight confirm that 1) the service was refreshed in the same server (new PID) 2) the server was indeed using 6GB RAM prior to the problem.

I checked boot.ini and it was fine.

crasc

gbn SSCertifiable Points: 6429 More actions · Answer 7

fail over your cluster and reboot the first node?

Clovis Celestin SSC Enthusiast Points: 141 More actions · Answer 8

The issue has been resolved by shutting down the service and restarting it on the same node. I am trying to figure out why it happened. I could not find any documents to this effects. I just want to make sure that I am not hitting a bug and trying to proactive.

crasc