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)
 Help Needed - Insufficient memory available

Author  Topic 

cykhaw
Starting Member

1 Post

Posted - 2010-06-09 : 01:36:20
Hi Experts,

Could any of you help in this memory issue. I have been searching around for weeks without resolution or workaround.

Environment Details:
- Microsoft SQL Server 2000 - 8.00.2282 (Intel X86) Dec 30 2008 02:22:41 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
- Windows Server 2003 R2 Enterprise Edition Service Pack 2, Intel Xeon 2.53GHz 8 CPU, 64GB memory, PAE
- In boot.ini: \WINDOWS="Windows Server 2003, Enterprise" /noexecute=optout /fastdetect /PAE /redirect
- sp_configure: 'awe enabled' 1, 'min server memory (MB)' 1024, 'max server memory (MB)' 61440, 'max degree of parallelism', '3'
- Granted "Lock Pages in Memory" Privilege to the Service Account under which SQL Server starts
- Dedicated server for the default instance of SQL Server and only 1 DB is created. No other Apps is using this server
- Total 2759 tables are created for the DB (Actually is a Siebel DB)
- No index rebuild jobs, maintenance jobs or other jobs are running in SQL Server Agent when the insufficient memory issue happened. No Linked Servers created
- More than 2GB memory are left for OS. No CPU bound. DB files reside in SAN drive. 8 temp files (4GB each) created for tempdb with equal size. Data files, temp files, Log files are on different drives.

In Windows Log:
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 6/8/2010
Time: 7:28:57 PM
User: N/A
Description:
Error: 17803, Severity: 20, State: 12
Insufficient memory available.

In SQL Server Log:
2010-06-08 19:10:00.83 backup Log backed up: Database: crmprod, creation date(time): 2009/09/05(04:37:32), first LSN: 127734:44307:1, last LSN: 127734:48590:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'F:\MSSQL\BACKUP\CRMPROD\crmprod_tlog_201006081910.TRN'}).
2010-06-08 19:20:01.28 backup Log backed up: Database: crmprod, creation date(time): 2009/09/05(04:37:32), first LSN: 127734:48590:1, last LSN: 127735:2851:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'F:\MSSQL\BACKUP\CRMPROD\crmprod_tlog_201006081920.TRN'}).
2010-06-08 19:28:56.33 spid131 AWE mapping status:
2010-06-08 19:28:56.33 spid131 There are 139456 pages in the AWE window
2010-06-08 19:28:56.33 spid131 137770 pages have a bstat of 000000
2010-06-08 19:28:56.33 spid131 1451 pages have a bstat of 000009
2010-06-08 19:28:56.33 spid131 89 pages have a bstat of 001001
2010-06-08 19:28:56.33 spid131 74 pages have a bstat of 00000b
2010-06-08 19:28:56.33 spid131 65 pages have a bstat of 00008b
2010-06-08 19:28:56.33 spid131 7 pages have a bstat of 000020
2010-06-08 19:28:56.33 spid131 BPool::Map: no remappable address found.
2010-06-08 19:28:56.39 spid121 BPool::Map: no remappable address found.
2010-06-08 19:28:56.43 spid284 BPool::Map: no remappable address found.
2010-06-08 19:28:56.43 spid410 BPool::Map: no remappable address found.
2010-06-08 19:28:56.46 spid131 Buffer Distribution: Stolen=132573 Free=14 Procedures=5287
Inram=0 Dirty=1800379 Kept=0
I/O=0, Latched=739, Other=5925328
2010-06-08 19:28:56.46 spid131 Buffer Counts: Commited=7864320 Target=7864320 Hashed=7726446
InternalReservation=1212 ExternalReservation=230 Min Free=1024 Visible= 139456
2010-06-08 19:28:56.46 spid131 Procedure Cache: TotalProcs=89 TotalPages=5287 InUsePages=5259
2010-06-08 19:28:56.46 spid131 Dynamic Memory Manager: Stolen=137859 OS Reserved=7688
OS Committed=7666
OS In Use=7362
Query Plan=58220 Optimizer=71888
General=11194
Utilities=8 Connection=3626
2010-06-08 19:28:56.46 spid131 Global Memory Objects: Resource=7072 Locks=153
SQLCache=517 Replication=2
LockBytes=2 ServerGlobal=33
Xact=91
2010-06-08 19:28:56.46 spid131 Query Memory Manager: Grants=2 Waiting=1 Maximum=230 Available=0
2010-06-08 19:28:56.47 spid131 BPool::Map: no remappable address found.
2010-06-08 19:28:56.60 spid131 Buffer Distribution: Stolen=132573 Free=14 Procedures=5287
Inram=0 Dirty=1800379 Kept=0
I/O=0, Latched=741, Other=5925326
2010-06-08 19:28:56.60 spid131 Buffer Counts: Commited=7864320 Target=7864320 Hashed=7726446
InternalReservation=1212 ExternalReservation=230 Min Free=1024 Visible= 139456
2010-06-08 19:28:56.60 spid131 Procedure Cache: TotalProcs=89 TotalPages=5287 InUsePages=5259
2010-06-08 19:28:56.60 spid131 Dynamic Memory Manager: Stolen=137859 OS Reserved=7688
OS Committed=7666
OS In Use=7362
Query Plan=58220 Optimizer=71888
General=11194
Utilities=8 Connection=3626
2010-06-08 19:28:56.60 spid131 Global Memory Objects: Resource=7072 Locks=153
SQLCache=517 Replication=2
LockBytes=2 ServerGlobal=33
Xact=91
2010-06-08 19:28:56.60 spid131 Query Memory Manager: Grants=2 Waiting=1 Maximum=230 Available=0
2010-06-08 19:28:56.60 spid131 BPool::Map: no remappable address found.
2010-06-08 19:28:56.72 spid131 Buffer Distribution: Stolen=132574 Free=13 Procedures=5287
Inram=0 Dirty=1800379 Kept=0
I/O=0, Latched=741, Other=5925326
2010-06-08 19:28:56.72 spid131 Buffer Counts: Commited=7864320 Target=7864320 Hashed=7726446
InternalReservation=1212 ExternalReservation=230 Min Free=1024 Visible= 139456
2010-06-08 19:28:56.72 spid131 Procedure Cache: TotalProcs=89 TotalPages=5287 InUsePages=5259
2010-06-08 19:28:56.72 spid131 Dynamic Memory Manager: Stolen=137859 OS Reserved=7688
OS Committed=7666
OS In Use=7362
Query Plan=58220 Optimizer=71888
General=11194
Utilities=8 Connection=3626
2010-06-08 19:28:56.72 spid131 Global Memory Objects: Resource=7072 Locks=153
SQLCache=517 Replication=2
LockBytes=2 ServerGlobal=33
Xact=91
2010-06-08 19:28:56.72 spid131 Query Memory Manager: Grants=2 Waiting=1 Maximum=230 Available=0
2010-06-08 19:28:56.72 spid340 BPool::Map: no remappable address found.
2010-06-08 19:28:56.74 spid131 BPool::Map: no remappable address found.
2010-06-08 19:28:56.74 spid54 BPool::Map: no remappable address found.
2010-06-08 19:28:56.74 spid1 BPool::Map: no remappable address found.
2010-06-08 19:28:56.83 spid495 BPool::Map: no remappable address found.
2010-06-08 19:28:56.86 spid131 Buffer Distribution: Stolen=132578 Free=115 Procedures=5248
Inram=0 Dirty=1800379 Kept=0
I/O=0, Latched=741, Other=5925259
2010-06-08 19:28:56.86 spid131 Buffer Counts: Commited=7864320 Target=7864320 Hashed=7726379
InternalReservation=1251 ExternalReservation=230 Min Free=1024 Visible= 139456
2010-06-08 19:28:56.86 spid131 Procedure Cache: TotalProcs=83 TotalPages=5248 InUsePages=5244
2010-06-08 19:28:56.86 spid131 Dynamic Memory Manager: Stolen=137822 OS Reserved=7688
OS Committed=7666
OS In Use=7360
Query Plan=58181 Optimizer=71888
General=11194
Utilities=8 Connection=3626
2010-06-08 19:28:56.86 spid131 Global Memory Objects: Resource=7072 Locks=153
SQLCache=517 Replication=2
LockBytes=2 ServerGlobal=33
Xact=91
2010-06-08 19:28:56.86 spid131 Query Memory Manager: Grants=2 Waiting=1 Maximum=230 Available=0
2010-06-08 19:28:56.86 spid131 BPool::Map: no remappable address found.
2010-06-08 19:28:56.91 spid196 BPool::Map: no remappable address found.
2010-06-08 19:28:57.00 spid131 Buffer Distribution: Stolen=132579 Free=114 Procedures=5248
Inram=0 Dirty=1800379 Kept=0
I/O=0, Latched=741, Other=5925259
2010-06-08 19:28:57.00 spid131 Buffer Counts: Commited=7864320 Target=7864320 Hashed=7726379
InternalReservation=1251 ExternalReservation=230 Min Free=1024 Visible= 139456
2010-06-08 19:28:57.00 spid131 Procedure Cache: TotalProcs=83 TotalPages=5248 InUsePages=5244
2010-06-08 19:28:57.00 spid131 Dynamic Memory Manager: Stolen=137822 OS Reserved=7688
OS Committed=7666
OS In Use=7360
Query Plan=58181 Optimizer=71888
General=11194
Utilities=8 Connection=3626
2010-06-08 19:28:57.00 spid131 Global Memory Objects: Resource=7072 Locks=153
SQLCache=517 Replication=2
LockBytes=2 ServerGlobal=33
Xact=91
2010-06-08 19:28:57.00 spid131 Query Memory Manager: Grants=2 Waiting=1 Maximum=230 Available=0
2010-06-08 19:28:57.00 spid131 BPool::Map: no remappable address found.
2010-06-08 19:28:57.05 spid196 Buffer Distribution: Stolen=132579 Free=114 Procedures=5248
Inram=0 Dirty=1800379 Kept=0
I/O=0, Latched=741, Other=5925259
2010-06-08 19:28:57.05 spid196 Buffer Counts: Commited=7864320 Target=7864320 Hashed=7726379
InternalReservation=1251 ExternalReservation=230 Min Free=1024 Visible= 139456
2010-06-08 19:28:57.05 spid196 Procedure Cache: TotalProcs=83 TotalPages=5248 InUsePages=5244
2010-06-08 19:28:57.05 spid196 Dynamic Memory Manager: Stolen=137822 OS Reserved=7688
OS Committed=7666
OS In Use=7360
Query Plan=58181 Optimizer=71888
General=11194
Utilities=8 Connection=3626
2010-06-08 19:28:57.05 spid196 Global Memory Objects: Resource=7072 Locks=153
SQLCache=517 Replication=2
LockBytes=2 ServerGlobal=33
Xact=91
2010-06-08 19:28:57.05 spid196 Query Memory Manager: Grants=2 Waiting=1 Maximum=230 Available=0
2010-06-08 19:28:57.05 spid196 Error: 17803, Severity: 20, State: 12
2010-06-08 19:28:57.05 spid196 Insufficient memory available..
2010-06-08 19:28:57.08 spid196 BPool::Map: no remappable address found.
2010-06-08 19:28:57.15 spid131 Buffer Distribution: Stolen=132572 Free=121 Procedures=5248











   

- Advertisement -