DBCC MemoryStatus dump and help 'Insufficient memory'

Hey guys. Today morning at about 9:58am I had an insufficient memory exception in SQL. I've reserved 12gb memory for my SQL server. I've pasted below the dbcc MemoryStatus dump gotten from SQL log. It also gave me a BPool::Map: No remappable address found

My SQL Server version is 8.00.2148. Enterprise Ed. Can you see any problems in the below pasted log?

2006-08-09 09:58:11.63 spid175 BPool::Map: no remappable address found.
2006-08-09 09:58:11.69 spid101 Buffer Distribution: Stolen=156843 Free=1634 Procedures=17623
Inram=0 Dirty=108669 Kept=0
I/O=0, Latched=1220, Other=1286875
2006-08-09 09:58:11.69 spid101 Buffer Counts: Commited=1572864 Target=1572864 Hashed=1396751
InternalReservation=510 ExternalReservation=130 Min Free=1552 Visible= 185824
2006-08-09 09:58:11.69 spid101 Procedure Cache: TotalProcs=3811 TotalPages=17623 InUsePages=16353
2006-08-09 09:58:11.69 spid101 Dynamic Memory Manager: Stolen=151680 OS Reserved=1432
OS Committed=1397
OS In Use=1387
Query Plan=90752 Optimizer=21
General=17603
Utilities=3220 Connection=10805
2006-08-09 09:58:11.69 spid101 Global Memory Objects: Resource=6560 Locks=31195
SQLCache=651 Replication=2
LockBytes=2 ServerGlobal=22
Xact=1021
2006-08-09 09:58:11.69 spid101 Query Memory Manager: Grants=2 Waiting=0 Maximum=26799 Available=3906

Thank you.

Are you running a server app on this SQL Server machine?|||I don't quite understand your question but I think this is what you are asking. There are 4 machines which could acces the db server. Two of these are the web servers. The front app is written in vb.net. The other two are backend application servers which have windows services installed on them. What they do is, they read data from a file and depending on the data, do some ins,upd,del etc. I hope I answered your question...|||Hi Tej, I experienced a similar problem a week back. The performance

counters were normal but it ran out of memory for some reason. A

restart fixed it but I don't know the cause.|||

Tej,

I'm having the same issue on build 2187. I'm working with PSS on resolution (SLOWLY). It's my theory that the optimizer is the root issue as dbcc memorystatus is bringing back huge swings within a minute or two timeframe (from 0 to 125,000 buffers) just before the 701 error pops into the applog. Have you been able to reproduce this issue or find a 701 error message in your application log? It doesn't show up in the sql server error log... but there is a good command for getting a mini-dump during the 701 errors. Simply use dbcc dumptrigger ('set',701) . I wouldn't recommend using this "just because" but it will help MS PSS further diagnose your problem.

|||

Determining the root cause of a memory issue is sometimes difficult.

From the output in the original log it seems there are a number of contributing factors.

Here's the breakdown on that system.

There are 185824 (Visible= 185824) buffers in virtual address space that can be mapped in at any one time. This is a normal value on 32 bit systems.

Of those buffers 156843 are "stolen" (Stolen=156843) - or in use by a component other than the buffer pool.

Stolen buffers can further be broken down:

- Procedure cache 17623
- Query Plan=90752
- Connection= 10805
- Locks=31195

Procedure cache and query plan could well be driven up by a large number of adhoc queries that are not parameterized.

The connection size indicates there are a fair amount of client connected.

The memory stolen for locks is large, but not abnormal.

Resolving the insufficient memory issues here will most likely involve some tuning of the apps to use better parameterized queries, and improving query plans to reduce the number of locks taken.

|||

Hi, I have a similar question in that our server only shows 100MB of memory being used by SQL when AWE is enabled and we have 8GB in the box with a max setting of 7GB. We have PAE enabled and the boot.ini is below as well. Does this dump mean that SQL is only using 104MB of memory at the moment?

Thanks in advance,

Scott

multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /fastdetect /noexecute=OptIn /PAE

Here is our dump.

Memory Manager KB

--

VM Reserved 1674140

VM Committed 104384

AWE Allocated 6747704

Reserved Memory 1024

Reserved Memory In Use 0

(5 row(s) affected)

Memory node Id = 0 KB

--

VM Reserved 3456

VM Committed 34976

AWE Allocated 2540400

MultiPage Allocator 3264

SinglePage Allocator 183128

(5 row(s) affected)

Memory node Id = 1 KB

--

VM Reserved 1666588

VM Committed 65460

AWE Allocated 4207304

MultiPage Allocator 9632

SinglePage Allocator 183128

|||

I presume you see the 100MB value through task manager or perfmon. This is normal when AWE is enabled as these tools do not account for physical page allocations done by applications.

Have a look at the AWE Allocated entry. It indicates that over 6GB has been allocated.

AWE Allocated 6747704

Another way to confirm is to look at the SQL Server:Memory Manager\Total Server memory(KB) counter from performance monitor.

DBCC MemoryStatus dump and help 'Insufficient memory'

Hey guys. Today morning at about 9:58am I had an insufficient memory exception in SQL. I've reserved 12gb memory for my SQL server. I've pasted below the dbcc MemoryStatus dump gotten from SQL log. It also gave me a BPool::Map: No remappable address found

My SQL Server version is 8.00.2148. Enterprise Ed. Can you see any problems in the below pasted log?

2006-08-09 09:58:11.63 spid175 BPool::Map: no remappable address found.
2006-08-09 09:58:11.69 spid101 Buffer Distribution: Stolen=156843 Free=1634 Procedures=17623
Inram=0 Dirty=108669 Kept=0
I/O=0, Latched=1220, Other=1286875
2006-08-09 09:58:11.69 spid101 Buffer Counts: Commited=1572864 Target=1572864 Hashed=1396751
InternalReservation=510 ExternalReservation=130 Min Free=1552 Visible= 185824
2006-08-09 09:58:11.69 spid101 Procedure Cache: TotalProcs=3811 TotalPages=17623 InUsePages=16353
2006-08-09 09:58:11.69 spid101 Dynamic Memory Manager: Stolen=151680 OS Reserved=1432
OS Committed=1397
OS In Use=1387
Query Plan=90752 Optimizer=21
General=17603
Utilities=3220 Connection=10805
2006-08-09 09:58:11.69 spid101 Global Memory Objects: Resource=6560 Locks=31195
SQLCache=651 Replication=2
LockBytes=2 ServerGlobal=22
Xact=1021
2006-08-09 09:58:11.69 spid101 Query Memory Manager: Grants=2 Waiting=0 Maximum=26799 Available=3906

Thank you.

Are you running a server app on this SQL Server machine?|||I don't quite understand your question but I think this is what you are asking. There are 4 machines which could acces the db server. Two of these are the web servers. The front app is written in vb.net. The other two are backend application servers which have windows services installed on them. What they do is, they read data from a file and depending on the data, do some ins,upd,del etc. I hope I answered your question...|||Hi Tej, I experienced a similar problem a week back. The performance

counters were normal but it ran out of memory for some reason. A

restart fixed it but I don't know the cause.|||

Tej,

I'm having the same issue on build 2187. I'm working with PSS on resolution (SLOWLY). It's my theory that the optimizer is the root issue as dbcc memorystatus is bringing back huge swings within a minute or two timeframe (from 0 to 125,000 buffers) just before the 701 error pops into the applog. Have you been able to reproduce this issue or find a 701 error message in your application log? It doesn't show up in the sql server error log... but there is a good command for getting a mini-dump during the 701 errors. Simply use dbcc dumptrigger ('set',701) . I wouldn't recommend using this "just because" but it will help MS PSS further diagnose your problem.

|||

Determining the root cause of a memory issue is sometimes difficult.

From the output in the original log it seems there are a number of contributing factors.

Here's the breakdown on that system.

There are 185824 (Visible= 185824) buffers in virtual address space that can be mapped in at any one time. This is a normal value on 32 bit systems.

Of those buffers 156843 are "stolen" (Stolen=156843) - or in use by a component other than the buffer pool.

Stolen buffers can further be broken down:

- Procedure cache 17623
- Query Plan=90752
- Connection= 10805
- Locks=31195

Procedure cache and query plan could well be driven up by a large number of adhoc queries that are not parameterized.

The connection size indicates there are a fair amount of client connected.

The memory stolen for locks is large, but not abnormal.

Resolving the insufficient memory issues here will most likely involve some tuning of the apps to use better parameterized queries, and improving query plans to reduce the number of locks taken.

|||

Hi, I have a similar question in that our server only shows 100MB of memory being used by SQL when AWE is enabled and we have 8GB in the box with a max setting of 7GB. We have PAE enabled and the boot.ini is below as well. Does this dump mean that SQL is only using 104MB of memory at the moment?

Thanks in advance,

Scott

multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /fastdetect /noexecute=OptIn /PAE

Here is our dump.

Memory Manager KB

--

VM Reserved 1674140

VM Committed 104384

AWE Allocated 6747704

Reserved Memory 1024

Reserved Memory In Use 0

(5 row(s) affected)

Memory node Id = 0 KB

--

VM Reserved 3456

VM Committed 34976

AWE Allocated 2540400

MultiPage Allocator 3264

SinglePage Allocator 183128

(5 row(s) affected)

Memory node Id = 1 KB

--

VM Reserved 1666588

VM Committed 65460

AWE Allocated 4207304

MultiPage Allocator 9632

SinglePage Allocator 183128

|||

I presume you see the 100MB value through task manager or perfmon. This is normal when AWE is enabled as these tools do not account for physical page allocations done by applications.

Have a look at the AWE Allocated entry. It indicates that over 6GB has been allocated.

AWE Allocated 6747704

Another way to confirm is to look at the SQL Server:Memory Manager\Total Server memory(KB) counter from performance monitor.

DBCC MemoryStatus dump and help 'Insufficient memory'

Hey guys. Today morning at about 9:58am I had an insufficient memory exception in SQL. I've reserved 12gb memory for my SQL server. I've pasted below the dbcc MemoryStatus dump gotten from SQL log. It also gave me a BPool::Map: No remappable address found

My SQL Server version is 8.00.2148. Enterprise Ed. Can you see any problems in the below pasted log?

2006-08-09 09:58:11.63 spid175 BPool::Map: no remappable address found.
2006-08-09 09:58:11.69 spid101 Buffer Distribution: Stolen=156843 Free=1634 Procedures=17623
Inram=0 Dirty=108669 Kept=0
I/O=0, Latched=1220, Other=1286875
2006-08-09 09:58:11.69 spid101 Buffer Counts: Commited=1572864 Target=1572864 Hashed=1396751
InternalReservation=510 ExternalReservation=130 Min Free=1552 Visible= 185824
2006-08-09 09:58:11.69 spid101 Procedure Cache: TotalProcs=3811 TotalPages=17623 InUsePages=16353
2006-08-09 09:58:11.69 spid101 Dynamic Memory Manager: Stolen=151680 OS Reserved=1432
OS Committed=1397
OS In Use=1387
Query Plan=90752 Optimizer=21
General=17603
Utilities=3220 Connection=10805
2006-08-09 09:58:11.69 spid101 Global Memory Objects: Resource=6560 Locks=31195
SQLCache=651 Replication=2
LockBytes=2 ServerGlobal=22
Xact=1021
2006-08-09 09:58:11.69 spid101 Query Memory Manager: Grants=2 Waiting=0 Maximum=26799 Available=3906

Thank you.

Are you running a server app on this SQL Server machine?|||I don't quite understand your question but I think this is what you are asking. There are 4 machines which could acces the db server. Two of these are the web servers. The front app is written in vb.net. The other two are backend application servers which have windows services installed on them. What they do is, they read data from a file and depending on the data, do some ins,upd,del etc. I hope I answered your question...|||Hi Tej, I experienced a similar problem a week back. The performance

counters were normal but it ran out of memory for some reason. A

restart fixed it but I don't know the cause.|||

Tej,

I'm having the same issue on build 2187. I'm working with PSS on resolution (SLOWLY). It's my theory that the optimizer is the root issue as dbcc memorystatus is bringing back huge swings within a minute or two timeframe (from 0 to 125,000 buffers) just before the 701 error pops into the applog. Have you been able to reproduce this issue or find a 701 error message in your application log? It doesn't show up in the sql server error log... but there is a good command for getting a mini-dump during the 701 errors. Simply use dbcc dumptrigger ('set',701) . I wouldn't recommend using this "just because" but it will help MS PSS further diagnose your problem.

|||

Determining the root cause of a memory issue is sometimes difficult.

From the output in the original log it seems there are a number of contributing factors.

Here's the breakdown on that system.

There are 185824 (Visible= 185824) buffers in virtual address space that can be mapped in at any one time. This is a normal value on 32 bit systems.

Of those buffers 156843 are "stolen" (Stolen=156843) - or in use by a component other than the buffer pool.

Stolen buffers can further be broken down:

- Procedure cache 17623
- Query Plan=90752
- Connection= 10805
- Locks=31195

Procedure cache and query plan could well be driven up by a large number of adhoc queries that are not parameterized.

The connection size indicates there are a fair amount of client connected.

The memory stolen for locks is large, but not abnormal.

Resolving the insufficient memory issues here will most likely involve some tuning of the apps to use better parameterized queries, and improving query plans to reduce the number of locks taken.

|||

Hi, I have a similar question in that our server only shows 100MB of memory being used by SQL when AWE is enabled and we have 8GB in the box with a max setting of 7GB. We have PAE enabled and the boot.ini is below as well. Does this dump mean that SQL is only using 104MB of memory at the moment?

Thanks in advance,

Scott

multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /fastdetect /noexecute=OptIn /PAE

Here is our dump.

Memory Manager KB

--

VM Reserved 1674140

VM Committed 104384

AWE Allocated 6747704

Reserved Memory 1024

Reserved Memory In Use 0

(5 row(s) affected)

Memory node Id = 0 KB

--

VM Reserved 3456

VM Committed 34976

AWE Allocated 2540400

MultiPage Allocator 3264

SinglePage Allocator 183128

(5 row(s) affected)

Memory node Id = 1 KB

--

VM Reserved 1666588

VM Committed 65460

AWE Allocated 4207304

MultiPage Allocator 9632

SinglePage Allocator 183128

|||

I presume you see the 100MB value through task manager or perfmon. This is normal when AWE is enabled as these tools do not account for physical page allocations done by applications.

Have a look at the AWE Allocated entry. It indicates that over 6GB has been allocated.

AWE Allocated 6747704

Another way to confirm is to look at the SQL Server:Memory Manager\Total Server memory(KB) counter from performance monitor.

DBCC DBREINDEX fails for table

Hi,

I am facing a rather peculiar issue where I am getting a floating point exception error while rebuilding index for a particular table.

---
Error Number : 3628

Message :

[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3628: [Microsoft][ODBC SQL Server Driver][SQL Server]A floating point exception occurred in the user process. Current transaction is canceled.

[Microsoft][ODBC SQL Server
----
This seems to be a rare issue ( as acknowledged by microsoft ) and they seem to suggest that it happens with SQL Server 2000 SP3 . I migrated my database into an SQL Server 2000 SP4 and started the rebuild again .. .. But it still failed with the same error ..

Am just hoping the microsoft guys are wrong and many of you have actually faced this stuff before.. Please let me know.

Thanks in advance,
Ranjit.That's one I have not come accross. Have a look at this thread, and see if you can follow the same steps:

http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=70&threadid=45658&enterthread=y

Are the columns in the index float datatypes?|||What about an INDEXDEFRAG instead of a rebuild?

Regards,

hmscott|||My bet is that it is a corrupt value for a floating point number... Not all possible values are valid floating point values. Some of them are non-sensical, so any attempt to even read them produces a runtime error.

A simple test would be to do a SELECT * to see if you can successfully retrieve all of the rows.

If that is the case, you'll need to fix the row before you can successfully build the index.

-PatP|||Thanks MCrowley/Scott/Pat for all your help.
But I am not done yet and would be back after some more research.. maybe to ask you guys again or to let you what I did to overcome my issue :)

Thanks once again,
Ranjit.|||Just an update on this one ..

Well this was due to corrupt float data in the tables ..to track the errant row ..we tried to export it into a file from where we could see it .. so we tried various methods of export ..

we could not export it into an xls/textfile, As the no of rows were large, ..
export thru bcp was possible but we could not correct the data as it was in native format .. import through BCP failed surprisingly long before it encountered the corrupt data rows .. ( maybe some mismatch in the data during bcp export )

But the select statement was the best solution in identifying the corrupt rows .. SQL failed to select on the corrupt data rows .. and we knew the error lay in those rows ..

Once identified, an update command was successfully executed on the corrupt rows and rectified ..

PS: all this was done on a test database .. we still have to sit down with the business team and make the changes ..

Just wanted to know what could be the best way out

1) change the data in corrupt rows and make sure their application inputs correct data
2) alter the column of the tables to accomodate the data ..

Please let me know.

And Thanks once again for the wonderful help I recieved during this issue.

Warm Regards,
Ranjit.|||One option is to simply "plug" the offending values. If you know that column plugh of the row associated with PK 'xyzzy' is bad, you can simply:UPDATE myTable
SET plugh = 0e0
WHERE 'xyzzy' = PK

Another approach is to make a copy, the basic idea is simple, but implementing it can be a bit difficult to explain. The short answer is to copy all of the completely readable rows in the original table to a scratch table, then copy the columns you can get from the probem row or rows substituting some acceptable value for the problem columns.

The exact mechanics of this process get complicated, due to space/time/other constraints. Feel free to ask for more help if you run into problems, because there are often easy fixes for otherwise unsolvable problems if you're willing to "think outside the box" a bit!

-PatP