SQL out of memory - 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)

SQL out of memory

Author

Topic

JohnDeere
Posting Yak Master

191 Posts

Posted - 2007-05-17 : 18:36:29

This morning the following information appeared in the sql log
This happened again at aroung 10:00am similar messages I can post if needed. The 2nd time sql server shutdown.
The server is an 8 CPU with 32 GB of RAM in an active\active cluster.
The sql server was configured to use 28 GB of RAM using AWE
SQL 2000 EE
Windows 2003 Advanced server.

After the 2nd incident the server was restarted and the sql memory was reduced to 16 GB. The memory change was already planned. It was needed to make the cluster failover correctly.
We have a case open with PSS but they are indicating a query caused this. They suggested we run the PSS DIAG tool to capture the queries when we think the issue is about to happen. At this time we do not have any idea how to predict when this will happen. They do not recommend running the tool all the time because the capture files get to large and the capture process can affect system performance and response times.

I had perfmon runnning unfortunately the problems only last a minute or 2 (perfmon is logging every minute) so I don't have a lot of data but the data I have AWE counters CPU, etc. all look normal.

2007-05-17 07:28:26.56 spid130 BPool::Map: no remappable address found.
2007-05-17 07:28:26.58 spid104 BPool::Map: no remappable address found.
2007-05-17 07:28:26.58 spid80 AWE mapping status:
2007-05-17 07:28:26.58 spid80 There are 157072 pages in the AWE window
2007-05-17 07:28:26.58 spid80 156255 pages have a bstat of 000000
2007-05-17 07:28:26.58 spid80 569 pages have a bstat of 000009
2007-05-17 07:28:26.58 spid80 96 pages have a bstat of 00000b
2007-05-17 07:28:26.58 spid80 76 pages have a bstat of 001001
2007-05-17 07:28:26.58 spid80 50 pages have a bstat of 00060d
2007-05-17 07:28:26.58 spid80 16 pages have a bstat of 000209
2007-05-17 07:28:26.58 spid80 8 pages have a bstat of 000020
2007-05-17 07:28:26.58 spid80 2 pages have a bstat of 00040d
2007-05-17 07:28:26.58 spid80 BPool::Map: no remappable address found.
2007-05-17 07:28:27.42 spid70 BPool::Map: no remappable address found.
2007-05-17 07:28:27.50 spid104 Buffer Distribution: Stolen=156227 Free=1952 Procedures=183
Inram=0 Dirty=122258 Kept=0
I/O=0, Latched=303, Other=3305125
2007-05-17 07:28:27.50 spid80 Buffer Distribution: Stolen=156227 Free=1952 Procedures=183
Inram=0 Dirty=122258 Kept=0
2007-05-17 07:28:27.50 spid104 Buffer Counts: Commited=3586048 Target=3586048 Hashed=3427686
InternalReservation=718 ExternalReservation=473 Min Free=2048 Visible= 157072
I/O=0, Latched=303, Other=3305125
2007-05-17 07:28:27.50 spid104 Procedure Cache: TotalProcs=54 TotalPages=183 InUsePages=116
2007-05-17 07:28:27.50 spid80 Buffer Counts: Commited=3586048 Target=3586048 Hashed=3427686
InternalReservation=718 ExternalReservation=473 Min Free=2048 Visible= 157072
2007-05-17 07:28:27.50 spid104 Dynamic Memory Manager: Stolen=156209 OS Reserved=1192
OS Committed=1170
OS In Use=1091
2007-05-17 07:28:27.50 spid80 Procedure Cache: TotalProcs=54 TotalPages=183 InUsePages=116
Query Plan=266 Optimizer=116302
General=5793
Utilities=145 Connection=804
2007-05-17 07:28:27.50 spid80 Dynamic Memory Manager: Stolen=156209 OS Reserved=1192
OS Committed=1170
OS In Use=1091
Query Plan=266 Optimizer=116302
General=5793
Utilities=145 Connection=804
2007-05-17 07:28:27.50 spid104 Global Memory Objects: Resource=1476 Locks=34577
SQLCache=3975 Replication=2
LockBytes=2 ServerGlobal=39
Xact=161
2007-05-17 07:28:27.50 spid80 Global Memory Objects: Resource=1476 Locks=34577
SQLCache=3975 Replication=2
2007-05-17 07:28:27.50 spid104 Query Memory Manager: Grants=2 Waiting=0 Maximum=14721 Available=14082
LockBytes=2 ServerGlobal=39
Xact=161
2007-05-17 07:28:27.50 spid80 Query Memory Manager: Grants=2 Waiting=0 Maximum=14721 Available=14082
2007-05-17 07:28:27.58 spid104 BPool::Map: no remappable address found.
2007-05-17 07:28:27.59 spid80 BPool::Map: no remappable address found.
2007-05-17 07:28:27.89 spid130 Buffer Distribution: Stolen=156223 Free=2091 Procedures=48
Inram=0 Dirty=122258 Kept=0
I/O=0, Latched=303, Other=3305125
2007-05-17 07:28:27.89 spid130 Buffer Counts: Commited=3586048 Target=3586048 Hashed=3427684
InternalReservation=725 ExternalReservation=473 Min Free=2048 Visible= 157072
2007-05-17 07:28:27.89 spid130 Procedure Cache: TotalProcs=54 TotalPages=48 InUsePages=28
2007-05-17 07:28:27.89 spid130 Dynamic Memory Manager: Stolen=156103 OS Reserved=1192
OS Committed=1170
OS In Use=1091
Query Plan=144 Optimizer=116316
General=5780
Utilities=145 Connection=804
2007-05-17 07:28:27.89 spid130 Global Memory Objects: Resource=1476 Locks=34592
SQLCache=3975 Replication=2
LockBytes=2 ServerGlobal=39
Xact=161
2007-05-17 07:28:27.89 spid130 Query Memory Manager: Grants=2 Waiting=0 Maximum=14721 Available=14082
2007-05-17 07:28:28.48 spid104 Buffer Distribution: Stolen=156243 Free=2194 Procedures=82
Inram=0 Dirty=122258 Kept=0
I/O=0, Latched=303, Other=3304968
2007-05-17 07:28:28.48 spid104 Buffer Counts: Commited=3586048 Target=3586048 Hashed=3427529
InternalReservation=725 ExternalReservation=473 Min Free=2048 Visible= 157072
2007-05-17 07:28:28.48 spid104 Procedure Cache: TotalProcs=28 TotalPages=82 InUsePages=39
2007-05-17 07:28:28.48 spid104 Dynamic Memory Manager: Stolen=156154 OS Reserved=1192
OS Committed=1170
OS In Use=1091
Query Plan=165 Optimizer=116331
General=5795
Utilities=145 Connection=804
2007-05-17 07:28:28.48 spid104 Global Memory Objects: Resource=1476 Locks=34592
SQLCache=3975 Replication=2
LockBytes=2 ServerGlobal=39
Xact=161
2007-05-17 07:28:28.48 spid104 Query Memory Manager: Grants=2 Waiting=0 Maximum=14721 Available=14082
2007-05-17 07:28:28.48 spid80 Buffer Distribution: Stolen=156244 Free=2193 Procedures=82
Inram=0 Dirty=122258 Kept=0
I/O=0, Latched=303, Other=3304968
2007-05-17 07:28:28.48 spid80 Buffer Counts: Commited=3586048 Target=3586048 Hashed=3427529
InternalReservation=725 ExternalReservation=473 Min Free=2048 Visible= 157072
2007-05-17 07:28:28.48 spid80 Procedure Cache: TotalProcs=28 TotalPages=82 InUsePages=39
2007-05-17 07:28:28.48 spid80 Dynamic Memory Manager: Stolen=156155 OS Reserved=1192
OS Committed=1170
OS In Use=1091
Query Plan=165 Optimizer=116331
General=5795
Utilities=145 Connection=804
2007-05-17 07:28:28.48 spid80 Global Memory Objects: Resource=1476 Locks=34594
SQLCache=3975 Replication=2
LockBytes=2 ServerGlobal=39
Xact=161
2007-05-17 07:28:28.48 spid80 Query Memory Manager: Grants=2 Waiting=0 Maximum=14721 Available=14082
2007-05-17 07:28:28.51 spid104 BPool::Map: no remappable address found.
2007-05-17 07:28:28.51 spid130 BPool::Map: no remappable address found.
2007-05-17 07:28:28.55 spid80 BPool::Map: no remappable address found.
2007-05-17 07:28:29.06 spid83 BPool::Map: no remappable address found.
2007-05-17 07:28:29.34 spid70 Buffer Distribution: Stolen=156257 Free=2200 Procedures=62
Inram=0 Dirty=122258 Kept=0
I/O=0, Latched=302, Other=3304969
2007-05-17 07:28:29.34 spid70 Buffer Counts: Commited=3586048 Target=3586048 Hashed=3427529
InternalReservation=725 ExternalReservation=473 Min Free=2032 Visible= 157072
2007-05-17 07:28:29.34 spid70 Procedure Cache: TotalProcs=37 TotalPages=62 InUsePages=37
2007-05-17 07:28:29.34 spid70 Dynamic Memory Manager: Stolen=156148 OS Reserved=1192
OS Committed=1170
OS In Use=1091
Query Plan=147 Optimizer=116340
General=5797
Utilities=145 Connection=804
2007-05-17 07:28:29.34 spid70 Global Memory Objects: Resource=1476 Locks=34594
SQLCache=3975 Replication=2
LockBytes=2 ServerGlobal=39
Xact=161
2007-05-17 07:28:29.34 spid70 Query Memory Manager: Grants=2 Waiting=0 Maximum=14721 Available=14082
2007-05-17 07:28:29.36 spid70 BPool::Map: no remappable address found.
2007-05-17 07:28:29.44 spid104 Buffer Distribution: Stolen=156257 Free=2200 Procedures=62
Inram=0 Dirty=122258 Kept=0
I/O=0, Latched=300, Other=3304971
2007-05-17 07:28:29.44 spid104 Buffer Counts: Commited=3586048 Target=3586048 Hashed=3427529
InternalReservation=725 ExternalReservation=473 Min Free=2032 Visible= 157072
2007-05-17 07:28:29.44 spid104 Procedure Cache: TotalProcs=28 TotalPages=62 InUsePages=37
2007-05-17 07:28:29.44 spid104 Dynamic Memory Manager: Stolen=156148 OS Reserved=1192
OS Committed=1170
OS In Use=1091
Query Plan=147 Optimizer=116340
General=5797
Utilities=145 Connection=804
2007-05-17 07:28:29.44 spid104 Global Memory Objects: Resource=1476 Locks=34594
SQLCache=3975 Replication=2
LockBytes=2 ServerGlobal=39
Xact=161
2007-05-17 07:28:29.44 spid104 Query Memory Manager: Grants=2 Waiting=0 Maximum=14721 Available=14082
2007-05-17 07:28:29.47 spid104 BPool::Map: no remappable address found.
2007-05-17 07:28:29.70 spid92 BPool::Map: no remappable address found.
2007-05-17 07:28:30.14 spid80 Buffer Distribution: Stolen=156259 Free=2226 Procedures=34
Inram=0 Dirty=122258 Kept=0
I/O=0, Latched=300, Other=3304971
2007-05-17 07:28:30.14 spid80 Buffer Counts: Commited=3586048 Target=3586048 Hashed=3427529
InternalReservation=722 ExternalReservation=473 Min Free=2016 Visible= 157072
2007-05-17 07:28:30.14 spid80 Procedure Cache: TotalProcs=28 TotalPages=34 InUsePages=28
2007-05-17 07:28:30.14 spid80 Dynamic Memory Manager: Stolen=156122 OS Reserved=1192
OS Committed=1170
OS In Use=1091
Query Plan=136 Optimizer=116340
General=5784
Utilities=145 Connection=802
2007-05-17 07:28:30.14 spid80 Global Memory Objects: Resource=1476 Locks=34595
SQLCache=3975 Replication=2
LockBytes=2 ServerGlobal=39
Xact=161
2007-05-17 07:28:30.14 spid80 Query Memory Manager: Grants=2 Waiting=0 Maximum=14721 Available=14082
2007-05-17 07:28:30.20 spid80 BPool::Map: no remappable address found.
2007-05-17 07:28:30.25 spid130 Buffer Distribution: Stolen=156273 Free=2212 Procedures=34
Inram=0 Dirty=122258 Kept=0
I/O=0, Latched=301, Other=3304970
2007-05-17 07:28:30.25 spid130 Buffer Counts: Commited=3586048 Target=3586048 Hashed=3427529
InternalReservation=722 ExternalReservation=473 Min Free=2016 Visible= 157072
2007-05-17 07:28:30.25 spid130 Procedure Cache: TotalProcs=28 TotalPages=34 InUsePages=28
2007-05-17 07:28:30.25 spid130 Dynamic Memory Manager: Stolen=156136 OS Reserved=1192
OS Committed=1170
OS In Use=1091
Query Plan=136 Optimizer=116354
General=5784
Utilities=145 Connection=802
2007-05-17 07:28:30.25 spid130 Global Memory Objects: Resource=1476 Locks=34595
SQLCache=3975 Replication=2
LockBytes=2 ServerGlobal=39
Xact=161
2007-05-17 07:28:30.25 spid130 Query Memory Manager: Grants=2 Waiting=0 Maximum=14721 Available=14082
2007-05-17 07:28:30.41 spid104 Buffer Distribution: Stolen=156252 Free=2231 Procedures=36
Inram=0 Dirty=122258 Kept=0
I/O=0, Latched=303, Other=3304968
2007-05-17 07:28:30.41 spid104 Buffer Counts: Commited=3586048 Target=3586048 Hashed=3427529
InternalReservation=722 ExternalReservation=473 Min Free=2016 Visible= 157072
2007-05-17 07:28:30.41 spid104 Procedure Cache: TotalProcs=9 TotalPages=36 InUsePages=29
2007-05-17 07:28:30.41 spid104 Dynamic Memory Manager: Stolen=156118 OS Reserved=1192
OS Committed=1170
OS In Use=1091
Query Plan=138 Optimizer=116337
General=5781
Utilities=145 Connection=802
2007-05-17 07:28:30.41 spid104 Global Memory Objects: Resource=1476 Locks=34595
SQLCache=3975 Replication=2
LockBytes=2 ServerGlobal=39
Xact=161
2007-05-17 07:28:30.41 spid104 Query Memory Manager: Grants=2 Waiting=0 Maximum=14721 Available=14082
2007-05-17 07:28:30.41 spid104 Error: 1204, Severity: 19, State: 1
2007-05-17 07:28:30.41 spid104 The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration..
2007-05-17 07:28:30.64 spid92 Buffer Distribution: Stolen=156112 Free=2361 Procedures=46
Inram=0 Dirty=122258 Kept=0
I/O=0, Latched=302, Other=3304969
2007-05-17 07:28:30.64 spid92 Buffer Counts: Commited=3586048 Target=3586048 Hashed=3427529
InternalReservation=722 ExternalReservation=197 Min Free=2016 Visible= 157072
2007-05-17 07:28:30.64 spid92 Procedure Cache: TotalProcs=7 TotalPages=46 InUsePages=32
2007-05-17 07:28:30.64 spid92 Dynamic Memory Manager: Stolen=156131 OS Reserved=1192
OS Committed=1170
OS In Use=1091
Query Plan=145 Optimizer=116337
General=5786
Utilities=145 Connection=803
2007-05-17 07:28:30.64 spid92 Global Memory Objects: Resource=1476 Locks=34595
SQLCache=3975 Replication=2
LockBytes=2 ServerGlobal=39
Xact=161
2007-05-17 07:28:30.64 spid92 Query Memory Manager: Grants=1 Waiting=0 Maximum=14721 Available=14500
2007-05-17 07:28:30.72 spid83 Buffer Distribution: Stolen=156114 Free=2350 Procedures=55
Inram=0 Dirty=122258 Kept=0
I/O=0, Latched=302, Other=3304969
2007-05-17 07:28:30.72 spid83 Buffer Counts: Commited=3586048 Target=3586048 Hashed=3427529
InternalReservation=722 ExternalReservation=197 Min Free=2016 Visible= 157072
2007-05-17 07:28:30.72 spid83 Procedure Cache: TotalProcs=9 TotalPages=55 InUsePages=32
2007-05-17 07:28:30.72 spid83 Dynamic Memory Manager: Stolen=156143 OS Reserved=1192
OS Committed=1170
OS In Use=1091
Query Plan=150 Optimizer=116337
General=5792
Utilities=145 Connection=804
2007-05-17 07:28:30.72 spid83 Global Memory Objects: Resource=1476 Locks=34595
SQLCache=3975 Replication=2
LockBytes=2 ServerGlobal=39
Xact=161
2007-05-17 07:28:30.72 spid83 Query Memory Manager: Grants=1 Waiting=0 Maximum=14721 Available=14500
2007-05-17 07:28:30.76 spid70 Buffer Distribution: Stolen=156132 Free=2317 Procedures=70
Inram=0 Dirty=122258 Kept=0
I/O=0, Latched=301, Other=3304970
2007-05-17 07:28:30.76 spid70 Buffer Counts: Commited=3586048 Target=3586048 Hashed=3427529
InternalReservation=722 ExternalReservation=197 Min Free=2016 Visible= 157072
2007-05-17 07:28:30.76 spid70 Procedure Cache: TotalProcs=7 TotalPages=70 InUsePages=43
2007-05-17 07:28:30.76 spid70 Dynamic Memory Manager: Stolen=156180 OS Reserved=1192
OS Committed=1170
OS In Use=1091
Query Plan=177 Optimizer=116343
General=5795
Utilities=145 Connection=805
2007-05-17 07:28:30.76 spid70 Global Memory Objects: Resource=1476 Locks=34595
SQLCache=3975 Replication=2
LockBytes=2 ServerGlobal=39
Xact=161
2007-05-17 07:28:30.76 spid70 Query Memory Manager: Grants=1 Waiting=0 Maximum=14721 Available=14500
2007-05-17 07:28:30.95 spid9 BPool::Map: no remappable address found.
2007-05-17 07:28:31.05 spid83 BPool::Map: no remappable address found.
2007-05-17 07:28:31.11 spid70 BPool::Map: no remappable address found.
2007-05-17 07:28:31.11 spid62 BPool::Map: no remappable address found.
2007-05-17 07:28:31.12 spid101 BPool::Map: no remappable address found.
2007-05-17 07:28:31.14 spid130 BPool::Map: no remappable address found.
2007-05-17 07:28:31.87 spid9 Buffer Distribution: Stolen=156161 Free=2406 Procedures=78
Inram=0 Dirty=122258 Kept=0
I/O=0, Latched=305, Other=3304840
2007-05-17 07:28:31.87 spid9 Buffer Counts: Commited=3586048 Target=3586048 Hashed=3427403
InternalReservation=723 ExternalReservation=197 Min Free=2000 Visible= 157072
2007-05-17 07:28:31.87 spid9 Procedure Cache: TotalProcs=32 TotalPages=78 InUsePages=57
2007-05-17 07:28:31.87 spid9 Dynamic Memory Manager: Stolen=156210 OS Reserved=1192
OS Committed=1170
OS In Use=1091
Query Plan=188 Optimizer=116368
General=5789
Utilities=145 Connection=805
2007-05-17 07:28:31.87 spid9 Global Memory Objects: Resource=1476 Locks=34595
SQLCache=3975 Replication=2
LockBytes=2 ServerGlobal=39
Xact=161
2007-05-17 07:28:31.87 spid9 Query Memory Manager: Grants=1 Waiting=2 Maximum=221 Available=0
2007-05-17 07:28:31.92 spid9 BPool::Map: no remappable address found.
2007-05-17 07:28:32.06 spid62 Buffer Distribution: Stolen=156162 Free=2405 Procedures=78
Inram=0 Dirty=122258 Kept=0
I/O=0, Latched=305, Other=3304840
2007-05-17 07:28:32.06 spid62 Buffer Counts: Commited=3586048 Target=3586048 Hashed=3427403
InternalReservation=723 ExternalReservation=197 Min Free=2000 Visible= 157072
2007-05-17 07:28:32.06 spid62 Procedure Cache: TotalProcs=32 TotalPages=78 InUsePages=57
2007-05-17 07:28:32.06 spid62 Dynamic Memory Manager: Stolen=156210 OS Reserved=1192
OS Committed=1170
OS In Use=1091
Query Plan=188 Optimizer=116368
General=5789
Utilities=145 Connection=805
2007-05-17 07:28:32.06 spid62 Global Memory Objects: Resource=1476 Locks=34595
SQLCache=3975 Replication=2
LockBytes=2 ServerGlobal=39
Xact=161
2007-05-17 07:28:32.06 spid62 Query Memory Manager: Grants=1 Waiting=2 Maximum=221 Available=0
2007-05-17 07:28:32.41 spid130 Buffer Distribution: Stolen=156150 Free=2445 Procedures=50
Inram=0 Dirty=122258 Kept=0
I/O=0, Latched=305, Other=3304840
2007-05-17 07:28:32.41 spid130 Buffer Counts: Commited=3586048 Target=3586048 Hashed=3427403
InternalReservation=727 ExternalReservation=197 Min Free=1984 Visible= 157072
2007-05-17 07:28:32.41 spid130 Procedure Cache: TotalProcs=32 TotalPages=50 InUsePages=45
2007-05-17 07:28:32.41 spid130 Dynamic Memory Manager: Stolen=156171 OS Reserved=1192
OS Committed=1170
OS In Use=1091
Query Plan=169 Optimizer=116367
General=5776
Utilities=145 Connection=799
2007-05-17 07:28:32.41 spid130 Global Memory Objects: Resource=1476 Locks=34595
SQLCache=3974 Replication=2
LockBytes=2 ServerGlobal=39
Xact=161
2007-05-17 07:28:32.41 spid130 Query Memory Manager: Grants=1 Waiting=2 Maximum=221 Available=0
2007-05-17 07:28:32.42 spid70 Buffer Distribution: Stolen=156150 Free=2445 Procedures=50
Inram=0 Dirty=122258 Kept=0
I/O=0, Latched=305, Other=3304840
2007-05-17 07:28:32.42 spid70 Buffer Counts: Commited=3586048 Target=3586048 Hashed=3427403
InternalReservation=727 ExternalReservation=197 Min Free=1984 Visible= 157072
2007-05-17 07:28:32.42 spid70 Procedure Cache: TotalProcs=32 TotalPages=50 InUsePages=45
2007-05-17 07:28:32.42 spid70 Dynamic Memory Manager: Stolen=156171 OS Reserved=1192
OS Committed=1170
OS In Use=1091
Query Plan=169 Optimizer=116367
General=5776
Utilities=145 Connection=799
2007-05-17 07:28:32.42 spid70 Global Memory Objects: Resource=1476 Locks=34595
SQLCache=3974 Replication=2
LockBytes=2 ServerGlobal=39
Xact=161
2007-05-17 07:28:32.42 spid70 Query Memory Manager: Grants=1 Waiting=2 Maximum=221 Available=0
2007-05-17 07:28:32.42 spid83 Buffer Distribution: Stolen=156150 Free=2445 Procedures=50
Inram=0 Dirty=122258 Kept=0
I/O=0, Latched=305, Other=3304840
2007-05-17 07:28:32.42 spid83 Buffer Counts: Commited=3586048 Target=3586048 Hashed=3427403
InternalReservation=727 ExternalReservation=197 Min Free=1984 Visible= 157072
2007-05-17 07:28:32.42 spid83 Procedure Cache: TotalProcs=32 TotalPages=50 InUsePages=45
2007-05-17 07:28:32.42 spid83 Dynamic Memory Manager: Stolen=156172 OS Reserved=1192
OS Committed=1170
OS In Use=1091
Query Plan=169 Optimizer=116368
General=5776
Utilities=145 Connection=799
2007-05-17 07:28:32.42 spid83 Global Memory Objects: Resource=1476 Locks=34595
SQLCache=3974 Replication=2
LockBytes=2 ServerGlobal=39
Xact=161
2007-05-17 07:28:32.42 spid83 Query Memory Manager: Grants=1 Waiting=2 Maximum=221 Available=0
2007-05-17 07:28:32.48 spid83 BPool::Map: no remappable address found.
2007-05-17 07:28:32.59 spid70 BPool::Map: no remappable address found.
2007-05-17 07:28:32.62 spid130 BPool::Map: no remappable address found.
2007-05-17 07:28:32.84 spid9 Buffer Distribution: Stolen=156166 Free=2429 Procedures=50
Inram=0 Dirty=122258 Kept=0
I/O=0, Latched=303, Other=3304842
2007-05-17 07:28:32.84 spid9 Buffer Counts: Commited=3586048 Target=3586048 Hashed=3427403
InternalReservation=727 ExternalReservation=197 Min Free=1984 Visible= 157072
2007-05-17 07:28:32.84 spid9 Procedure Cache: TotalProcs=13 TotalPages=50 InUsePages=45
2007-05-17 07:28:32.84 spid9 Dynamic Memory Manager: Stolen=156187 OS Reserved=1192
OS Committed=1170
OS In Use=1091
Query Plan=170 Optimizer=116379
General=5779
Utilities=145 Connection=799
2007-05-17 07:28:32.84 spid9 Global Memory Objects: Resource=1476 Locks=34595
SQLCache=3974 Replication=2
LockBytes=2 ServerGlobal=39
Xact=161
2007-05-17 07:28:32.84 spid9 Query Memory Manager: Grants=1 Waiting=2 Maximum=221 Available=0
2007-05-17 07:28:32.87 spid9 BPool::Map: no remappable address found.
2007-05-17 07:28:32.95 spid80 Buffer Distribution: Stolen=156166 Free=2429 Procedures=50
Inram=0 Dirty=122258 Kept=0
I/O=0, Latched=304, Other=3304841
2007-05-17 07:28:32.95 spid80 Buffer Counts: Commited=3586048 Target=3586048 Hashed=3427403
InternalReservation=727 ExternalReservation=197 Min Free=1984 Visible= 157072
2007-05-17 07:28:32.95 spid80 Procedure Cache: TotalProcs=32 TotalPages=50 InUsePages=45
2007-05-17 07:28:32.95 spid80 Dynamic Memory Manager: Stolen=156187 OS Reserved=1192
OS Committed=1170
OS In Use=1091
Query Plan=170 Optimizer=116379
General=5779
Utilities=145 Connection=799
2007-05-17 07:28:32.95 spid80 Global Memory Objects: Resource=1476 Locks=34595
SQLCache=3974 Replication=2
LockBytes=2 ServerGlobal=39
Xact=161
2007-05-17 07:28:32.95 spid80 Query Memory Manager: Grants=1 Waiting=2 Maximum=221 Available=0
2007-05-17 07:28:34.01 spid9 Buffer Distribution: Stolen=76370 Free=82174 Procedures=97
Inram=0 Dirty=122258 Kept=0
I/O=0, Latched=305, Other=3304844
2007-05-17 07:28:34.01 spid9 Buffer Counts: Commited=3586048 Target=3586048 Hashed=3427407
InternalReservation=726 ExternalReservation=197 Min Free=1984 Visible= 157072
2007-05-17 07:28:34.01 spid9 Procedure Cache: TotalProcs=28 TotalPages=97 InUsePages=54
2007-05-17 07:28:34.01 spid9 Dynamic Memory Manager: Stolen=76406 OS Reserved=1192
OS Committed=1170
OS In Use=1091
Query Plan=185 Optimizer=36540
General=5821
Utilities=145 Connection=799
2007-05-17 07:28:34.01 spid9 Global Memory Objects: Resource=1476 Locks=34595
SQLCache=3973 Replication=2
LockBytes=2 ServerGlobal=39
Xact=161
2007-05-17 07:28:34.01 spid9 Query Memory Manager: Grants=1 Waiting=2 Maximum=221 Available=0
2007-05-17 07:28:34.17 spid130 Buffer Distribution: Stolen=62717 Free=95827 Procedures=97
Inram=0 Dirty=122258 Kept=0
I/O=0, Latched=305, Other=3304844
2007-05-17 07:28:34.17 spid130 Buffer Counts: Commited=3586048 Target=3586048 Hashed=3427407
InternalReservation=726 ExternalReservation=197 Min Free=1984 Visible= 157072
2007-05-17 07:28:34.17 spid130 Procedure Cache: TotalProcs=28 TotalPages=97 InUsePages=54
2007-05-17 07:28:34.17 spid130 Dynamic Memory Manager: Stolen=62756 OS Reserved=1192
OS Committed=1170
OS In Use=1091
Query Plan=185 Optimizer=22890
General=5821
Utilities=145 Connection=799
2007-05-17 07:28:34.17 spid130 Global Memory Objects: Resource=1476 Locks=34595
SQLCache=3973 Replication=2
LockBytes=2 ServerGlobal=39
Xact=161
2007-05-17 07:28:34.17 spid130 Query Memory Manager: Grants=1 Waiting=2 Maximum=221 Available=0
2007-05-17 07:28:34.17 spid70 Buffer Distribution: Stolen=62654 Free=95890 Procedures=97
Inram=0 Dirty=122258 Kept=0
I/O=0, Latched=305, Other=3304844
2007-05-17 07:28:34.17 spid70 Buffer Counts: Commited=3586048 Target=3586048 Hashed=3427407
InternalReservation=726 ExternalReservation=197 Min Free=1984 Visible= 157072
2007-05-17 07:28:34.17 spid70 Procedure Cache: TotalProcs=28 TotalPages=97 InUsePages=54
2007-05-17 07:28:34.17 spid70 Dynamic Memory Manager: Stolen=62685 OS Reserved=1192
OS Committed=1170
OS In Use=1091
Query Plan=185 Optimizer=22817
General=5823
Utilities=145 Connection=800
2007-05-17 07:28:34.17 spid70 Global Memory Objects: Resource=1476 Locks=34595
SQLCache=3973 Replication=2
LockBytes=2 ServerGlobal=39
Xact=161
2007-05-17 07:28:34.17 spid70 Query Memory Manager: Grants=1 Waiting=2 Maximum=221 Available=0
2007-05-17 07:28:35.03 spid101 Buffer Distribution: Stolen=39716 Free=118819 Procedures=110
Inram=0 Dirty=122258 Kept=0
I/O=0, Latched=303, Other=3304842
2007-05-17 07:28:35.03 spid101 Buffer Counts: Commited=3586048 Target=3586048 Hashed=3427407
InternalReservation=727 ExternalReservation=197 Min Free=1984 Visible= 157072
2007-05-17 07:28:35.03 spid101 Procedure Cache: TotalProcs=14 TotalPages=110 InUsePages=66
2007-05-17 07:28:35.03 spid101 Dynamic Memory Manager: Stolen=39796 OS Reserved=1192
OS Committed=1170
OS In Use=1091
Query Plan=167 Optimizer=0
General=5769
Utilities=145 Connection=800
2007-05-17 07:28:35.03 spid101 Global Memory Objects: Resource=1476 Locks=34595
SQLCache=3973 Replication=2
LockBytes=2 ServerGlobal=39
Xact=161
2007-05-17 07:28:35.03 spid101 Query Memory Manager: Grants=1 Waiting=4 Maximum=26444 Available=26223
2007-05-17 07:28:35.44 spid83 Buffer Distribution: Stolen=39713 Free=118809 Procedures=118
Inram=0 Dirty=122258 Kept=0
I/O=0, Latched=300, Other=3304850
2007-05-17 07:28:35.44 spid83 Buffer Counts: Commited=3586048 Target=3586048 Hashed=3427412
InternalReservation=725 ExternalReservation=197 Min Free=1968 Visible= 157072
2007-05-17 07:28:35.44 spid83 Procedure Cache: TotalProcs=32 TotalPages=118 InUsePages=63
2007-05-17 07:28:35.44 spid83 Dynamic Memory Manager: Stolen=39802 OS Reserved=1192
OS Committed=1170
OS In Use=1091
Query Plan=173 Optimizer=0
General=5769
Utilities=145 Connection=800
2007-05-17 07:28:35.44 spid83 Global Memory Objects: Resource=1476 Locks=34595
SQLCache=3973 Replication=2
LockBytes=2 ServerGlobal=39
Xact=161
2007-05-17 07:28:35.44 spid83 Query Memory Manager: Grants=1 Waiting=5 Maximum=26444 Available=26223

Lance Harra

deepakontheweb
Starting Member

14 Posts

Posted - 2007-05-21 : 02:10:01

1. Did you find any critical events in system event logs, for this time period?
2. Can you run sp_configure and let us know the output.
3. was there any index rebuild job running when you recieved these errors?

Check this link for more details: http://support.microsoft.com/default.aspx/kb/838459

Deepak Kumar
SQL Server MVP, MCDBA

deepak.kumar@sqlknowledge.com

JohnDeere
Posting Yak Master

191 Posts

Posted - 2007-05-21 : 13:15:16

No index rebuilds were being performed.
Sp_configure is below.
Last night the backup times doubled but I could not find any reason why. THe DBCC's ran in the usual time.
CPU utilization was low. No AWE memory activity based on the perfmon counters. No other running processes that I could find.
The box is a dedicated sql server. No other applications installed.

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 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 16055 16055
max text repl size (B) 0 2147483647 65536 65536
max worker threads 32 32767 512 512
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 16055 16055
nested triggers 0 1 1 1
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 600 600
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

Lance Harra

Kristen
Test

22859 Posts

Posted - 2007-05-21 : 14:46:08

Sorry, but probably obvious questions:

Presumably its been running Just Fine for some time?

If so, what if anything changed shortly before the run-away server crash?

Any hardware issues in the Event Log? All drivers presumably "bang-up-to-date"?

You might want to post a LINK to this thread in the Data Corruption forum
http://www.sqlteam.com/forums/forum.asp?FORUM_ID=22
as there are some heavyweights who monitor that forum. (please don't re-post the question, folk will wind up reviewing and commenting in two places!)

Kristen

JohnDeere
Posting Yak Master

191 Posts

Posted - 2007-05-21 : 17:35:02

Nothing to my knowledge changed on the server or the OS.

The application log contained several of these messages.

Error: 17803, Severity: 20, State: 12
Insufficient memory available.

Lance Harra

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-21 : 23:48:40

Did you check perfmon to see how much memory does sql instance use?

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2007-05-22 : 00:24:09

What service pack are you running? I couldn't find it in your posts...
SELECT @@VERSION

http://support.microsoft.com/kb/815114
http://support.microsoft.com/kb/810052

DavidM

Production is just another testing cycle

JohnDeere
Posting Yak Master

191 Posts

Posted - 2007-05-22 : 14:17:43

We are running service pack 4 with AWE hotfix applied
Microsoft SQL Server 2000 - 8.00.2040 (Intel X86)
May 13 2005 18:33:17
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

Lance Harra

deepakontheweb
Starting Member

14 Posts

Posted - 2007-05-23 : 00:49:50

quote:
Originally posted by JohnDeere

We are running service pack 4 with AWE hotfix applied
Microsoft SQL Server 2000 - 8.00.2040 (Intel X86)
May 13 2005 18:33:17
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

Lance Harra

I assume in most of the cases either index rebuild operation or Extremely Large Number of User Tables on AWE System May Cause BPool::Map Errors.

http://support.microsoft.com/kb/319477

STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.This problem was first corrected in Microsoft SQL Server 2000 Service Pack 4.

Another keep checking the memory status(dbcc memorystatus), take help of perfmon and sql profiler to identify long running/resource consuming sql queries which may cause bpool overflow. if there have been no change in system configuration recently, then please look at recent database object(s) change.

Deepak Kumar
SQL Server MVP, MCDBA

deepak.kumar@sqlknowledge.com

JohnDeere
Posting Yak Master

191 Posts

Posted - 2007-05-23 : 07:25:08

Thanks

We were not running any reindex operations at the time of the problems.
We only have 700 tables in the the database. I hope that is not an extremely large number of tables.

Lance Harra

Kristen
Test

22859 Posts

Posted - 2007-05-23 : 13:43:36

"I hope that is not an extremely large number of tables"

No, it isn't. (It aint tiny either, but I guess you know that!)

Kristen

nheidorn
Starting Member

28 Posts

Posted - 2007-05-23 : 14:19:14

Somewhat off-topic, but I just love it when Microsoft uses measurements like "extremely large" in their knowledge base articles. How tough would it have been to put a number in there? They were obviously working with a specific customer issue when they wrote the article so the number of tables should have been a known value.

It serves no one to use an inspecific metric, particularly future Microsofties who run across this problem when dealing with customer support calls.

Kristen
Test

22859 Posts

Posted - 2007-05-23 : 14:58:11

Yeah, I agree. I've been looking for a "large"

dinning table on eBay for a while. So I have a search for "large dinning table" that emails me each day. I get about 20 hits a day, and about 1 a week that is useful

Mostly they are for things like "Large 4-seater dinning table" ... OK, so all four of you can put your elbows on the table on that one, but that wasn't really what I was looking for!

Kristen

JohnDeere
Posting Yak Master

191 Posts

Posted - 2007-05-23 : 19:48:29

Had another occurence today filled the log with similar messages for about 1 minute then stopped. SQL did not restart this time.
I did have a process running every minute to get the queries that were running so in theory I have the query that is causing the problem. Figuring out which one is another problem.
Is there a way to figure out how much memory a single query is using. All the memory counters I have heard about only list the memory in aggregate form.

Thanks

Lance Harra

deepakontheweb
Starting Member

14 Posts

Posted - 2007-05-24 : 00:03:12

quote:
Originally posted by nheidorn

Somewhat off-topic, but I just love it when Microsoft uses measurements like "extremely large" in their knowledge base articles. How tough would it have been to put a number in there? They were obviously working with a specific customer issue when they wrote the article so the number of tables should have been a known value.

It serves no one to use an inspecific metric, particularly future Microsofties who run across this problem when dealing with customer support calls.

Just to add little more here.. The link http://support.microsoft.com/kb/319477 clearly mention it "(typically 100000 or more)"

SYMPTOMS
On systems that have a large number of user tables (typically 100000 or more) that are running with Address Windowing Extensions (AWE) turned on, SQL Server may log the following error message in the SQL Server error log:

Deepak Kumar
SQL Server MVP, MCDBA
deepak.kumar@sqlknowledge.com

Disclaimer: This post is provided as is with no rights & warranty for accuracy, for the sake of knowledge sharing only.

deepakontheweb
Starting Member

14 Posts

Posted - 2007-05-24 : 00:25:20

quote:
Originally posted by JohnDeere

Had another occurence today filled the log with similar messages for about 1 minute then stopped. SQL did not restart this time.
I did have a process running every minute to get the queries that were running so in theory I have the query that is causing the problem. Figuring out which one is another problem.
Is there a way to figure out how much memory a single query is using. All the memory counters I have heard about only list the memory in aggregate form.

Thanks

Lance Harra

SQL BOL says:
One of the primary design goals of all database software is to minimize disk I/O because disk reads and writes are among the most resource-intensive operations. SQL Server builds a buffer cache in memory to hold pages read from the database. Much of the code in SQL Server is dedicated to minimizing the number of physical reads and writes between the disk and the buffer cache. The larger the buffer cache is, the less I/O SQL Server has to do to the database files.

Bases the above statement, if you find queries taking a lot DISKIO consider addressing those by performance tuning first. Minimizing DISKIO will help better memory usage and management.

You may run a trace with selected important counters from perfmon memory related counters:

Memory\% Committed Bytes In Use">
Memory\Available Bytes">
Memory\Available KBytes">
Memory\Available MBytes">
Memory\Cache Bytes">
Memory\Cache Bytes Peak">
Memory\Cache Faults/sec">
Memory\Commit Limit">
Memory\Committed Bytes">
Memory\Demand Zero Faults/sec">
Memory\Free System Page Table Entries">
Memory\Page Faults/sec">
Memory\Page Reads/sec">
Memory\Page Writes/sec">
Memory\Pages Input/sec">
Memory\Pages Output/sec">
Memory\Pages/sec">
Memory\Pool Nonpaged Allocs">
Memory\Pool Nonpaged Bytes">
Memory\Pool Paged Allocs">
Memory\Pool Paged Bytes">
Memory\Pool Paged Resident Bytes">
Memory\System Cache Resident Bytes">
Memory\System Code Resident Bytes">
Memory\System Code Total Bytes">
Memory\System Driver Resident Bytes">
Memory\System Driver Total Bytes">
Memory\Transition Faults/sec">
Memory\Write Copies/sec">
MSSQL$SQL2000:Memory Manager\Connection Memory (KB)">
MSSQL$SQL2000:Memory Manager\Granted Workspace Memory (KB)">
MSSQL$SQL2000:Memory Manager\Lock Blocks">
MSSQL$SQL2000:Memory Manager\Lock Blocks Allocated">
MSSQL$SQL2000:Memory Manager\Lock Memory (KB)">
MSSQL$SQL2000:Memory Manager\Lock Owner Blocks">
MSSQL$SQL2000:Memory Manager\Lock Owner Blocks Allocated">
MSSQL$SQL2000:Memory Manager\Maximum Workspace Memory (KB)">
MSSQL$SQL2000:Memory Manager\Memory Grants Outstanding">
MSSQL$SQL2000:Memory Manager\Memory Grants Pending">
MSSQL$SQL2000:Memory Manager\Optimizer Memory (KB)">
MSSQL$SQL2000:Memory Manager\SQL Cache Memory (KB)">
MSSQL$SQL2000:Memory Manager\Target Server Memory(KB)">
MSSQL$SQL2000:Memory Manager\Total Server Memory (KB)">
Paging File(_Total)\% Usage">
Paging File(_Total)\% Usage Peak">

As on your system you have configured sql min and max memory equal, therefore set the 'set working set size' SQL Server option to 1(currently its not set working set size 0 1 0 0). This can improve performance because SQL Server will not dynamically allocate memory.

http://www.databasejournal.com/features/mssql/article.php/1436951
http://blogs.msdn.com/slavao/archive/2005/02/11/371063.aspx

BTW, have you heard something from Microsoft PSS?

Deepak Kumar
SQL Server MVP, MCDBA
deepak.kumar@sqlknowledge.com

Disclaimer: This post is provided as is with no rights & warranty for accuracy, for the sake of knowledge sharing only.

JohnDeere
Posting Yak Master

191 Posts

Posted - 2007-05-24 : 00:58:28

I am using AWE and have configured the server to use 16GB of RAM
From BOL
Instances of SQL Server 2000 running in Address Windowing Extensions (AWE) memory mode do allocate all the full amount of memory specified in max server memory on server startup.

Yes, PSS wants me to run the DIAG tool when the server is having memory issues. Since I have no idea how to predict this and the incidents only span a minute or 2 I have not been successful in running the tool.

Lance Harra

nheidorn
Starting Member

28 Posts

Posted - 2007-05-24 : 10:56:30

quote:
Originally posted by deepakontheweb

Just to add little more here.. The link http://support.microsoft.com/kb/319477 clearly mention it "(typically 100000 or more)"

So it does. I missed that when I skimmed it the first time. My bad.

deepakontheweb
Starting Member

14 Posts

Posted - 2007-05-25 : 00:21:37

quote:
Originally posted by nheidorn

quote:
Originally posted by deepakontheweb

Just to add little more here.. The link http://support.microsoft.com/kb/319477 clearly mention it "(typically 100000 or more)"

So it does. I missed that when I skimmed it the first time. My bad.

It happens a lot time.. nothing to worry.

Deepak Kumar
SQL Server MVP, MCDBA
deepak.kumar@sqlknowledge.com

Disclaimer: This post is provided as is with no rights & warranty for accuracy, for the sake of knowledge sharing only.

deepakontheweb
Starting Member

14 Posts

Posted - 2007-05-28 : 04:55:02

quote:
Originally posted by JohnDeere

Had another occurence today filled the log with similar messages for about 1 minute then stopped. SQL did not restart this time.
I did have a process running every minute to get the queries that were running so in theory I have the query that is causing the problem. Figuring out which one is another problem.
Is there a way to figure out how much memory a single query is using. All the memory counters I have heard about only list the memory in aggregate form.

Thanks

Lance Harra

Please share your experience with us, which you got while dealing with this issue !

Deepak Kumar
SQL Server MVP, MCDBA
deepak.kumar@sqlknowledge.com

Disclaimer: This post is provided as is with no rights & warranty for accuracy, for the sake of knowledge sharing only.

JohnDeere
Posting Yak Master

191 Posts

Posted - 2007-06-03 : 23:57:23

The issue is still randomly occuring. Luckily sqlerver has not restarted during the latest incidents.

PSS is no help.
All they want is a pss diag while the event is occuring.
Since I can't predict it, it is hard to get PSS Diag running at the right time. Since the events are usually less than a minute I don't have time to start the PSS DIAG before the event is over.

I have built my own queries to capture DBCC inputbuffer, Memory and CPU information every minute and log it to a table.
I have looked at the queries that were running during the latest problem and they look like normal qureies that we run 100's of times a day.

I rebuilt all of our indexes last weekend and lowered the fill factor.
It did not help performance or change IO dramatically based on the counters I am collecting.

My latest effort is to reoptimize the queries that were running during the last problem and see if that makes a difference.

Lance Harra

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources