SQL Server Performance

Insufficient memory available in SQL 2000

Discussion in 'General DBA Questions' started by sramesh, Sep 29, 2009.

  1. sramesh New Member

    Hi , I have received below erro and one of the application has been terminated same time.SQL Version : 2000 Enterprise EditionService Pack: SP4Windows: Windows 2003 enterprise editionAWE is also enabled with 6 GB max memory. Total RAM: 8 GB. We didnt add /3GB key in boot.ini becuase it is lead us lot of issues.Low level hard ware test also performed and didnt find any issues. Memory modules are perfect. Please let me know the solution for this error.2009-09-28 06:23:17.64 spid123 Error: 17803, Severity: 20, State: 122009-09-28 06:23:17.64 spid123 Insufficient memory available..

    2009-09-28 09:51:02.23 spid143 Error: 17803, Severity: 20, State: 12
    2009-09-28 09:51:02.23 spid143 Insufficient memory available..
  2. Luis Martin Moderator

    How is memory setting in SQL?.
  3. sramesh New Member

    Please find memory setting for SQL 2000 enterprise edition.
    max server memory (MB) 4 2147483647 6144 6144
    min memory per query (KB) 512 2147483647 1024 1024
    min server memory (MB) 0 2147483647 0 0
  4. Luis Martin Moderator

    Well that is the problem.
    SQL only can reach 2gb of main memory. So you have to work with /3gb to get more memory because with 32 bits sql can reach up to 4GB.
    My recommendation is to move OS and SQL to 64 bits.

  5. sramesh New Member

    one month before i haved added /3GB and immediately we have found disconnection issues in application. Also we have faced perfomrnace issues in windows. We cant open task bar...... it is hung state....
    what is -g in startup parameter? it will resolve issues?
  6. moh_hassan20 New Member

    if you faced such errors:
    • "Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE <size>"
    • "Failed Virtual Allocate Bytes: FAIL_VIRTUAL_COMMIT <size>"
    -g may help , and that memory is used for COM+ or extended procedures, otherwise it may related to other issues which need to be investigated.
    Can you run dbcc memorystatus and dump the results
  7. sramesh New Member

    Hi,
    please find result for moh_hassan20
    and today integerity check job also failed with the error
    Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 701: [Microsoft][ODBC SQL Server Driver][SQL Server]There is insufficient system memory to run this query.
    The following errors were found:
    [Microsoft][ODBC SQL Server Driver][SQL Server]There is insufficient system memory to run this query.
    DBCC memorystatus
    ------------------------------------------------------------------------------
    Buffer Distribution Buffers
    ------------------------------ -----------
    Stolen 4428
    Free 3885
    Procedures 143083
    Inram 0
    Dirty 85721
    Kept 0
    I/O 0
    Latched 82
    Other 282556
    (9 row(s) affected)
    Buffer Counts Buffers
    ------------------------------ -----------
    Commited 519755
    Target 519755
    Hashed 368359
    InternalReservation 510
    ExternalReservation 0
    Min Free 1552
    Visible 199880
    (7 row(s) affected)
    Procedure Cache Value
    ------------------------------ -----------
    TotalProcs 4249
    TotalPages 143083
    InUsePages 36176
    (3 row(s) affected)
    Dynamic Memory Manager Buffers
    ------------------------------ -----------
    Stolen 147511
    OS Reserved 3080
    OS Committed 3058
    OS In Use 2968
    General 5543
    QueryPlan 143045
    Optimizer 0
    Utilities 140
    Connection 1218
    (9 row(s) affected)

    Global Memory Objects Buffers
    ------------------------------ -----------
    Resource 1115
    Locks 257
    XDES 67
    SQLCache 4251
    Replication 2
    LockBytes 2
    ServerGlobal 53
    (7 row(s) affected)

    Query Memory Objects Value
    ------------------------------ -----------
    Grants 0
    Waiting 0
    Available (Buffers) 119456
    Maximum (Buffers) 119456
    (4 row(s) affected)
    Optimization Queue Value
    ------------------------------ -----------
    Optimizing 0
    Waiting 0
    Available 64
    Maximum 64
    (4 row(s) affected)
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  8. sramesh New Member

    Currently we have applied SP4 in SQL server. AWE also enabled with 6 GB max memory. Server has 8 GB RAM but SQL utilized 4 GB ram because of bug. I think, SQL server is trying to use 6 GB ram but the bug is block memory. I hope this will resolve once I patch the post hot fix for SP4.
  9. moh_hassan20 New Member

    From memorystatus, the buffer pool use 4060.59 MB =~ 4GB , not all memory are used.
    the buffer pool Commited =Target = 519755 page = 4060.59 MB

    [quote user="sramesh"]Server has 8 GB RAM but SQL utilized 4 GB ram because of bug.[/quote]
    yes, You are right. it is documented in: http://support.microsoft.com/kb/899761
    Waiting to hear after installing the patch [;)]


  10. sramesh New Member

    Patch has been applied and minimum server memory has been set to 2 GB.After 10days, we are receiving insufficient error.
    Any body know woraround for this issue?
    is it possible to clear all data caches?
    i mean, i want to clear content upto 5 GB.
  11. moh_hassan20 New Member

    When that error happen? if there is memorystatus dump in log file , check it to find the consumers of memory
    Try to keep a record of memorystatus and review it after the problem occur.
    Check the size of the virtual memory paging file.
    You can set the initial size to less than the minimum recommended size .
    The recommended size is equivalent to 1.5 times the amount of RAM .
    Configure server , set min memory = max memory = 6G (or 7G if you set /3GB , use that option to get 1GB extra more)
    run DBCC FREEPROCCACHE

  12. sramesh New Member

    I didnt receive any memeory dump. SQL server is consumes 6 GB RAM and virtual memory paging also perfect ie assigned 12 GB.
    Do i need to apply all patches?
  13. satya Moderator

    Just a thought, do you have support contract with Microsoft in thsi case?
  14. sramesh New Member

    I didnt contact microsoft team becuase i dont know about license details. Even client also do not know about... :) .....

Share This Page