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