November 21, 2007 at 5:48 am
Hi all,
We have being having issues with a core database server over the last few weeks, and we have tried everything we can think of to get the database server to work as planned.
We have been getting a series of error messages (see Below), both in the windows event viewer and also sql error logs. But before I detail those, let me explain the server setup and configuration:
Server:
================================
Hardware: Dell Power Edge 6600, 8GB ECC DDR MEMORY, (8X1GB), 4 XEON 2.2GHZ/2MB L3 CACHE PROC. Originally Purchased 23/07/2004.
OS: Microsoft Windows Server 2003, Enterprise (still running sp1).
Startup options: "/fastdetect /3GB /PAE /NoExecute=OptOut"
Drive: [FileSys] [ Size ] [ Free ] [ Used ]
C$ NTFS 40924 26888 14036
D$ NTFS 70002 58826 11176
E$ NTFS 70002 65987 4015
F$ NTFS 98830 83208 15622
SQL:
================================
We have two editions running:
"SQL Server 2000 - 8.00.2040 (Intel X86)" (the one with the issues!)
and
"SQL Server 2005 - 9.00.1399.06 (Intel X86)"
Server 2000: has 83 databases (inc sys) and are mainly used for delivery of web dynamic content.
When the issues described below started happening, we upgraded from sp3a to this version.
Server 2005: has 2 databases also for web dynamic content.
There are no additional applications running (apart from av, etc).
Issues:
================================
The issues we have been having all started about two weeks ago, and we thought with the upgrade to sp4 (http://support.microsoft.com/?kbid=884525) and the additional AWE memory fix (http://support.microsoft.com/kb/899761/en-us) had done the trick, however after a week the issues have come back.
We did have the AWE enabled (with 6114 allocated as max server memory (MB)) when the issues started but have since removed this setting. Interesting note - when we booted the server with the 6144 allocated, it took as expected 6gb of Physical Memory (when viewed in things like cacti), but when restarting the service, it only took 2gb of Physical Memory and then slowly increased to 3gb, but did not take the full allocation.
With the new setting of "awe enabled=0" and "max server memory (MB)=2147483647" the service restart takes about 1gb and the ramps up as expected (to 3gb), but we know that it will at some point start producing the errors again... as below.
Errors:
================================
The types of errors we keep getting are:
#1:
WARNING: Failed to reserve contiguous memory of Size= 65536.
#2:
Buffer Distribution: Stolen=6309 Free=398446 Procedures=93539
Inram=0 Dirty=4786 Kept=0
I/O=0, Latched=311, Other=283041
Buffer Counts: Commited=786432 Target=786432 Hashed=288138
InternalReservation=821 ExternalReservation=3876 Min Free=256 Visible= 331160
Procedure Cache: TotalProcs=53243 TotalPages=93539 InUsePages=25041
Dynamic Memory Manager: Stolen=99553 OS Reserved=1336
OS Committed=1314
OS In Use=1311
Query Plan=93054 Optimizer=0
General=5745
Utilities=116 Connection=1803
Global Memory Objects: Resource=2198 Locks=108
SQLCache=2518 Replication=2
LockBytes=2 ServerGlobal=23
Xact=54
Query Memory Manager: Grants=3 Waiting=0 Maximum=225263 Available=221092
Buffer Distribution: Stolen=6313 Free=398446 Procedures=93535
Inram=0 Dirty=4786 Kept=0
I/O=0, Latched=310, Other=283042
Buffer Counts: Commited=786432 Target=786432 Hashed=288138
InternalReservation=821 ExternalReservation=3876 Min Free=256 Visible= 331160
Procedure Cache: TotalProcs=53243 TotalPages=93535 InUsePages=25041
Dynamic Memory Manager: Stolen=99553 OS Reserved=1336
OS Committed=1314
OS In Use=1311
Query Plan=93054 Optimizer=0
General=5745
Utilities=116 Connection=1803
Global Memory Objects: Resource=2198 Locks=108
SQLCache=2518 Replication=2
LockBytes=2 ServerGlobal=23
Xact=54
Query Memory Manager: Grants=3 Waiting=0 Maximum=225263 Available=221092
Buffer Distribution: Stolen=6366 Free=398446 Procedures=93482
Inram=0 Dirty=4786 Kept=0
I/O=0, Latched=310, Other=283042
Buffer Counts: Commited=786432 Target=786432 Hashed=288138
InternalReservation=821 ExternalReservation=3876 Min Free=256 Visible= 331160
Procedure Cache: TotalProcs=53243 TotalPages=93482 InUsePages=25041
Dynamic Memory Manager: Stolen=99553 OS Reserved=1336
OS Committed=1314
OS In Use=1311
Query Plan=93054 Optimizer=0
General=5745
Utilities=116 Connection=1803
Global Memory Objects: Resource=2198 Locks=108
SQLCache=2518 Replication=2
LockBytes=2 ServerGlobal=23
Xact=54
Query Memory Manager: Grants=3 Waiting=0 Maximum=225263 Available=221092
Buffer Distribution: Stolen=5968 Free=398934 Procedures=93411
Inram=0 Dirty=4784 Kept=0
I/O=0, Latched=311, Other=283024
Buffer Counts: Commited=786432 Target=786432 Hashed=288106
InternalReservation=821 ExternalReservation=4099 Min Free=256 Visible= 331160
Procedure Cache: TotalProcs=53232 TotalPages=93411 InUsePages=24975
Dynamic Memory Manager: Stolen=99320 OS Reserved=1312
OS Committed=1290
OS In Use=1260
Query Plan=92926 Optimizer=0
General=5618
Utilities=84 Connection=1806
Global Memory Objects: Resource=2198 Locks=108
SQLCache=2518 Replication=2
LockBytes=2 ServerGlobal=23
Xact=54
Query Memory Manager: Grants=3 Waiting=0 Maximum=225263 Available=221092
#3:
There is insufficient system memory to run this query. [SQLSTATE 42000] (Error 701) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
#4:
Event Type:Error
Event Source:MSSQLSERVER
Event Category:(2)
Event ID:17052
Date:20/11/2007
Time:10:21:00
User:N/A
Computer:[servername]
Description:
Error: 17803, Severity: 20, State: 4
Insufficient memory available.
#5:
Server: Msg 18452, Level 16, State 1, Line 0
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
Connection Broken
#6:
Error 22022: Unable to post notification to SQLServerAgent (reason: MapViewOfFile() returned error 8, 'Not enough storage is available to process this command.')
Additional information:
================================
sp_configure:
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
affinity mask -2147483648 2147483647 0 0
allow updates 0 1 0 0
awe enabled 0 1 1 1
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 23 23
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 0 0
nested triggers 0 1 0 0
network packet size (B) 512 32767 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 0 0
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 0 0
scan for startup procs 0 1 0 0
set working set size 0 1 0 0
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
Although please note that we have since (yesterday) disabled "awe enabled" in an attempt to get a stable system (as with "max server memory (MB)" we set this back to 2147483647).
DBCC MEMORYSTATUS:
Buffer Distribution Buffers
------------------------------ -----------
Stolen 3222
Free 1454
Procedures 19637
Inram 0
Dirty 4770
Kept 0
I/O 0
Latched 287
Other 161238
(9 row(s) affected)
Buffer Counts Buffers
------------------------------ -----------
Commited 190608
Target 336792
Hashed 166295
InternalReservation 797
ExternalReservation 0
Min Free 256
Visible 336792
(7 row(s) affected)
Procedure Cache Value
------------------------------ -----------
TotalProcs 10496
TotalPages 19637
InUsePages 7234
(3 row(s) affected)
Dynamic Memory Manager Buffers
------------------------------ -----------
Stolen 22859
OS Reserved 1024
OS Committed 1010
OS In Use 1006
General 2243
QueryPlan 19236
Optimizer 0
Utilities 83
Connection 2149
(9 row(s) affected)
Global Memory Objects Buffers
------------------------------ -----------
Resource 1310
Locks 96
XDES 40
SQLCache 412
Replication 2
LockBytes 2
ServerGlobal 30
(7 row(s) affected)
Query Memory Objects Value
------------------------------ -----------
Grants 0
Waiting 0
Available (Buffers) 244752
Maximum (Buffers) 244752
(4 row(s) affected)
Optimization Queue Value
------------------------------ -----------
Optimizing 0
Waiting 0
Available 32
Maximum 32
(4 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
================================
I have tried to include as much detail as possible, as from reading previous posting this level of detail is very important!
Any help gratefully received!
Many thanks in advance
Andy.
November 21, 2007 at 11:23 am
Failed to reserve contiguous memory of Size= 65536
Could be MemToLeaveArea is getting full. You can change the default from 256mb to 512mb with the -g switch
http://www.sqlnewsgroups.net/group/microsoft.public.sqlserver.server/topic1788.aspx
Overview of cumulative hotfixes for SP4:
July 8, 2009 at 9:09 am
You might try taking the /3GB out leaving only the PAE. Sometimes the additional overhead of both switches causes some funky things.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply