SQL Server Performance

Insufficient memory available..

Discussion in 'General DBA Questions' started by NewDBA, Sep 18, 2003.

  1. NewDBA New Member


    This is sequence of error in sQL log.
    I do have snapshot backup trird party software running continuastly on that server.
    Large number of dbs.
    What shoudl i do?
    1.Failed to reserve contiguous memory of Size= 1179648.

    2.Buffer Distribution: Stolen=8371 Free=107339 Procedures=53
    Inram=0 Dirty=8248 Kept=0
    I/O=0, Latched=1222, Other=83375

    3.Buffer Counts: Commited=208608 Target=208608 Hashed=92845
    InternalReservation=510 ExternalReservation=0 Min Free=19

    4.Procedure Cache: TotalProcs=23 TotalPages=53 InUsePages=7

    5.Dynamic Memory Manager: Stolen=8424 OS Reserved=2336
    OS Committed=4294945098
    OS In Use=1386
    Query Plan=46 Optimizer=120
    General=6640
    Utilities=930 Connection=2074

    6.Global Memory Objects: Resource=5978 Locks=69
    SQLCache=144 Replication=4
    LockBytes=2 ServerGlobal=55
    Xact=181

    7.Query Memory Manager: Grants=0 Waiting=0 Maximum=147780 Available=147780

    8.Error: 17803, Severity: 20, State: 12

    9.Insufficient memory available..
  2. Twan New Member

    Is this SQL7 or SQL2K what Service Pack, etc.

    This is generally due to a memory leak... there are several SQL Server bugs in older versions which can cause leakages, one such leak was when shrinking databases in SQL7, but there will be plenty more...

    I'd suggest restarting the box and then monitoring memory usage per process (using perfmon) and see which process has a steady climbing graph...

    Cheers
    Twan
  3. satya Moderator

  4. NewDBA New Member

    it's sql 2000 no sp.
  5. satya Moderator

    Then get it fixed by applying the latest SP as referred always.
    And check above options also.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  6. NewDBA New Member

    my server has virtual memory 2gb-4gb
    space available 6,5 gb
    what if i set max virtual memory to 6 gb


    Also
    "Reserve physical memory for sql server" is unticked
    should i specify this?
  7. gaurav_bindlish New Member

    Increasing the Virtual memory beyond recommended value has negative effects. I'll try to find a refernece to the same.

    BTW, what is the physical memory of the server? Is it dedicated SQL Server Box?

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  8. NewDBA New Member

    xp_mserv returns 1024 mb physical memory
    It is dedicated server but we running thirt party software to create hot backups
    for standby server (for high availability)

  9. gaurav_bindlish New Member

    I will not recommend using more than 3072 MB of page file.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  10. satya Moderator

    I would suggest and insist to test and apply the latest service pack to get rid of those errors which're corrected so. Because I would suspect from the above table points pinned about memory.

    Moreover you're using third party application for the backup which is using SNAPSHOT backup, is expected to consume memory resources.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  11. NewDBA New Member

    so actually i would rather try to decrease virtual memorey to 3 g than to increase to 4 g?
    I can't apply sp because company which suply the app will stop supporting it if our inviropment will defer from theirs and they do not have any sp installed.
    Isn't this wondefull?
  12. gaurav_bindlish New Member

    Let me tell you this...

    In addition to the performance anf functional issues, your server is vulnerable to virus attacks not to mention Slammer attack.

    So butter convince your vendor on the same...

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  13. NewDBA New Member

    Well i am trying hard to do this ...

    corrections
    xp_msver returns physical memory 1024 mb
    meanwhile total phisical memore has 2 gb
    so based on that can i increase virtual memory to 6 gb?
  14. gaurav_bindlish New Member

    I don't understand Why there is a difference between two?

    What is the physical memory on box, min and max memory and set working size configuration for SQL Server?

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  15. NewDBA New Member

    O!sorry physical memory 2 g
    I little in stress here:ran query on different server!
  16. satya Moderator

    That is really strange to hear from a third party which they don't support when using with SPs.
    Follow as Gaurav suggest in terms of VM settings.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  17. gaurav_bindlish New Member

    I have seen instances of this kind of behavior by third party vendors. I think while negotiating with third parties, one should keep the condition of application being run on latest service pack as a condition.

    Now in present case, If there are vulnerabilities in NewDBA's server, the team that has to face problem is NewDBA's team and not third party....

    This sucks..

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  18. satya Moderator

    I would also ask you to show the management about that KBA referred above which affected your system and get vendor to understand better about SPs.<br /><br />I believe thats how they make money.....[<img src='/community/emoticons/emotion-6.gif' alt=':(' />!]<br />_________<br />Satya SKJ<br />Moderator<br />SQL-Server-Performance.Com<br />
  19. NewDBA New Member

    The sad truth is that we appled sp3a once ...app stop working
    so we roll it a back
  20. satya Moderator

    I'm sure for the error point 1 & 8 is rectified under the hotfix and SPs.
    Is there any chance to stop the SNAPSHOT backup third-party application and look for the results.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  21. NewDBA New Member


    I know this problem started since we started to use this software.

    My question is if there is any chance to fix the problem without stopping backups.



    Should i restart the server after changing virtual memory setting?
  22. satya Moderator

    THen better to avoid this application for backups and depend on the legendary SQL statements for backups.

    Or addup more memory to get it running in terms of escape from above errors (few).

    Yes you should restart the server to affect the new VM settings, OS itself will prompt after the change.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  23. gaurav_bindlish New Member

    Yes, you need to restart the Box after changing virtual memory settings.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  24. NewDBA New Member

    thank you

Share This Page