Professional Web Applications Themes

There is insufficient system memory to run this query. - Microsoft SQL / MS SQL Server

Hi all, I'm having a recurring problem with my ASP/SQL Server 7 website. The website will be running smoothly, with a fairly consistent level of (externally visible) performance, until, after about a week, I'll suddenly start to get the following error... Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC SQL Server Driver][SQL Server]There is insufficient system memory to run this query. ....wherever a SQL query is made. Rebooting the SQL Server machine clears up the problem, until it reappears about a week later. Examining the SQL error log, everything seems fine for days, until the following messages start ...

  1. #1

    Default There is insufficient system memory to run this query.

    Hi all,

    I'm having a recurring problem with my ASP/SQL Server 7
    website. The website will be running smoothly, with a
    fairly consistent level of (externally visible)
    performance, until, after about a week, I'll suddenly
    start to get the following error...

    Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
    [Microsoft][ODBC SQL Server Driver][SQL Server]There is
    insufficient system memory to run this query.

    ....wherever a SQL query is made. Rebooting the SQL Server
    machine clears up the problem, until it reappears about a
    week later.

    Examining the SQL error log, everything seems fine for
    days, until the following messages start appearing each
    time a stored proc attempts execution:

    WARNING: Clearing procedure cache to free contiguous
    memory.

    Buffer Distribution: Stolen=3562 Free=128944 Procedures=20
    Inram=0 Dirty=69219 Kept=27
    I/O=0, Latched=0, Other=21420

    Buffer Counts: Commited=223192 Target=223192 Hashed=90666
    InternalReservation=82
    ExternalReservation=0 Min Free=81

    Procedure Cache: TotalProcs=2 TotalPages=20 InUsePages=19

    Dynamic Memory Manager: Stolen=3582 OS=439 General=3864
    Query Plan=77 Optimizer=6
    Utilities=5 Connection=69

    Global Memory Objects: Resource=443 Locks=1639 XDES=1
    SQLCache=94 tion=2
    LockBytes=2 ServerGlobal=20

    Query Memory Manager: Grants=0 Waiting=0 Maximum=161478
    Available=161478

    Does anyone speak the language of SQL Server internals,
    who can tell me what this means?

    Just from the behavior I'm guessing there's a loop or
    somesuch in my code that's eating procedure cache memory,
    but what's the best way to track and pinpoint the stored
    proc where it's occurring? I was thinking I could use
    Profiler to track the start and completion of procs, then
    get the longest running from that. But is there any way
    to see at a granular level what's impacting the memory?

    I've been going through KB articles as fast as I can read
    them (currently trying things suggested in "HOW TO:
    Troubleshoot Application Performance with SQL Server"
    and "HOW TO: Troubleshoot the Performance of Ad-Hoc
    Queries") but I'm hoping someone out there may have run
    into something similar before and be able to offer
    suggestions.

    This is SQL Server 7, running on a dedicated Windows 2000
    Server with 2GB RAM and dual 1.8 GHz processors. I'm
    using the Full Text Search add-in, and as per Microsoft's
    recommendation set the server's total virtual memory to 6
    GB and the SQL server max memory setting to 3 GB.

    Thanks for any and all advice,

    matt

    MattD Guest

  2. #2

    Default Re: There is insufficient system memory to run this query.

    - Which sql7 sp are you running ?
    - do your applications close and cleanup their db-objects ( connections,
    recordsetc, ....) ?

    jobi
    "MattD" <dreamking47> wrote in message
    news:07d701c34553$71080920$a001280aphx.gbl...
    > Hi all,
    >
    > I'm having a recurring problem with my ASP/SQL Server 7
    > website. The website will be running smoothly, with a
    > fairly consistent level of (externally visible)
    > performance, until, after about a week, I'll suddenly
    > start to get the following error...
    >
    > Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
    > [Microsoft][ODBC SQL Server Driver][SQL Server]There is
    > insufficient system memory to run this query.
    >
    > ...wherever a SQL query is made. Rebooting the SQL Server
    > machine clears up the problem, until it reappears about a
    > week later.
    >
    > Examining the SQL error log, everything seems fine for
    > days, until the following messages start appearing each
    > time a stored proc attempts execution:
    >
    > WARNING: Clearing procedure cache to free contiguous
    > memory.
    >
    > Buffer Distribution: Stolen=3562 Free=128944 Procedures=20
    > Inram=0 Dirty=69219 Kept=27
    > I/O=0, Latched=0, Other=21420
    >
    > Buffer Counts: Commited=223192 Target=223192 Hashed=90666
    > InternalReservation=82
    > ExternalReservation=0 Min Free=81
    >
    > Procedure Cache: TotalProcs=2 TotalPages=20 InUsePages=19
    >
    > Dynamic Memory Manager: Stolen=3582 OS=439 General=3864
    > Query Plan=77 Optimizer=6
    > Utilities=5 Connection=69
    >
    > Global Memory Objects: Resource=443 Locks=1639 XDES=1
    > SQLCache=94 tion=2
    > LockBytes=2 ServerGlobal=20
    >
    > Query Memory Manager: Grants=0 Waiting=0 Maximum=161478
    > Available=161478
    >
    > Does anyone speak the language of SQL Server internals,
    > who can tell me what this means?
    >
    > Just from the behavior I'm guessing there's a loop or
    > somesuch in my code that's eating procedure cache memory,
    > but what's the best way to track and pinpoint the stored
    > proc where it's occurring? I was thinking I could use
    > Profiler to track the start and completion of procs, then
    > get the longest running from that. But is there any way
    > to see at a granular level what's impacting the memory?
    >
    > I've been going through KB articles as fast as I can read
    > them (currently trying things suggested in "HOW TO:
    > Troubleshoot Application Performance with SQL Server"
    > and "HOW TO: Troubleshoot the Performance of Ad-Hoc
    > Queries") but I'm hoping someone out there may have run
    > into something similar before and be able to offer
    > suggestions.
    >
    > This is SQL Server 7, running on a dedicated Windows 2000
    > Server with 2GB RAM and dual 1.8 GHz processors. I'm
    > using the Full Text Search add-in, and as per Microsoft's
    > recommendation set the server's total virtual memory to 6
    > GB and the SQL server max memory setting to 3 GB.
    >
    > Thanks for any and all advice,
    >
    > matt
    >

    jobi Guest

  3. #3

    Default Re: There is insufficient system memory to run this query.

    If you can predict this behaviour, you could run profiler by the time you'd
    expect it to occur.

    jobi
    "MattD" <dreamking47> wrote in message
    news:0ad601c3456a$6428ef00$a001280aphx.gbl...
    > >-----Original Message-----
    > >- Which sql7 sp are you running ?
    >
    > Service Pack 4.
    >
    > >- do your applications close and cleanup their db-objects
    > ( connections,
    > >recordsetc, ....) ?
    >
    > Yes, I close them and set them to Nothing. It is possible
    > I missed one somewhere, of course. Is there any way of
    > determining this? There generally does not seem to be a
    > large number of open connections to SQL Server at any
    > given time, no more than 10-15 listed as "current
    > activity."
    >
    > Thanks,
    >
    > matt
    >
    > >> Hi all,
    > >>
    > >> I'm having a recurring problem with my ASP/SQL Server 7
    > >> website. The website will be running smoothly, with a
    > >> fairly consistent level of (externally visible)
    > >> performance, until, after about a week, I'll suddenly
    > >> start to get the following error...
    > >>
    > >> Microsoft OLE DB Provider for ODBC Drivers
    > error '80040e14'
    > >> [Microsoft][ODBC SQL Server Driver][SQL Server]There is
    > >> insufficient system memory to run this query.
    > >>
    > >> ...wherever a SQL query is made. Rebooting the SQL
    > Server
    > >> machine clears up the problem, until it reappears about
    > a
    > >> week later.
    > >>
    > >> Examining the SQL error log, everything seems fine for
    > >> days, until the following messages start appearing each
    > >> time a stored proc attempts execution:
    > >>
    > >> WARNING: Clearing procedure cache to free contiguous
    > >> memory.
    > >>
    > >> Buffer Distribution: Stolen=3562 Free=128944
    > Procedures=20
    > >> Inram=0 Dirty=69219 Kept=27
    > >> I/O=0, Latched=0, Other=21420
    > >>
    > >> Buffer Counts: Commited=223192 Target=223192
    > Hashed=90666
    > >> InternalReservation=82
    > >> ExternalReservation=0 Min Free=81
    > >>
    > >> Procedure Cache: TotalProcs=2 TotalPages=20
    > InUsePages=19
    > >>
    > >> Dynamic Memory Manager: Stolen=3582 OS=439 General=3864
    > >> Query Plan=77 Optimizer=6
    > >> Utilities=5 Connection=69
    > >>
    > >> Global Memory Objects: Resource=443 Locks=1639 XDES=1
    > >> SQLCache=94 tion=2
    > >> LockBytes=2 ServerGlobal=20
    > >>
    > >> Query Memory Manager: Grants=0 Waiting=0 Maximum=161478
    > >> Available=161478
    > >>
    > >> Does anyone speak the language of SQL Server internals,
    > >> who can tell me what this means?
    > >>
    > >> Just from the behavior I'm guessing there's a loop or
    > >> somesuch in my code that's eating procedure cache
    > memory,
    > >> but what's the best way to track and pinpoint the stored
    > >> proc where it's occurring? I was thinking I could use
    > >> Profiler to track the start and completion of procs,
    > then
    > >> get the longest running from that. But is there any way
    > >> to see at a granular level what's impacting the memory?
    > >>
    > >> I've been going through KB articles as fast as I can
    > read
    > >> them (currently trying things suggested in "HOW TO:
    > >> Troubleshoot Application Performance with SQL Server"
    > >> and "HOW TO: Troubleshoot the Performance of Ad-Hoc
    > >> Queries") but I'm hoping someone out there may have run
    > >> into something similar before and be able to offer
    > >> suggestions.
    > >>
    > >> This is SQL Server 7, running on a dedicated Windows
    > 2000
    > >> Server with 2GB RAM and dual 1.8 GHz processors. I'm
    > >> using the Full Text Search add-in, and as per
    > Microsoft's
    > >> recommendation set the server's total virtual memory to
    > 6
    > >> GB and the SQL server max memory setting to 3 GB.
    > >>
    > >> Thanks for any and all advice,
    > >>
    > >> matt
    > >>
    > >
    > >
    > >.
    > >

    jobi Guest

Similar Threads

  1. Insufficient system resources for PostgreSQL 7.4.x?
    By Ji Nmec in forum PostgreSQL / PGSQL
    Replies: 2
    Last Post: January 14th, 12:49 PM
  2. Insufficient memory for this operation.
    By Egyd Csaba in forum PostgreSQL / PGSQL
    Replies: 3
    Last Post: December 14th, 06:10 PM
  3. FH9 insufficient memory when launching ?
    By envirographics in forum Macromedia Freehand
    Replies: 1
    Last Post: June 16th, 10:01 PM
  4. Microsoft Word - Insufficient memory
    By Jon_Leer@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 1
    Last Post: April 23rd, 11:31 PM
  5. Insufficient Virtual Memory in Win98
    By Callum Ferguson in forum Adobe Photoshop Elements
    Replies: 0
    Last Post: June 24th, 09:23 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139