Re: Prepared sql generates "Insufficient memory available.." erro

From: AnthonyThomas (AnthonyThomas_at_discussions.microsoft.com)
Date: 01/06/05 

Date: Thu, 6 Jan 2005 13:57:04 -0800

Run DBCC MEMORYSTATUS and see how much space you have allocated for PROCCACHE.

Also, QA only returns a Firehouse, client-side cursor. Server-side, API
cursors introduce an excessive amount of overhead than even T-SQL cursors.
There should be no need for an application to create a Server-side, API
cursor. If you need functionality to run on the server, put it in a stored
procedure. If you need the API to scroll, put it in an API client-side
cursor.

Also, you said you had 3 GB of memory on the server. How has your SQL
Server configuration be set to use this memory? Also, are there any other
application components other than the SQL Server running on the host? They
may be keeping SQL Server from using any additional memory.

Sincerely,

Anthony Thomas

"TonyL" wrote:

> Hi Yogish,
> I have tried DBCC FREEPROCCACHE but to no avail.
> On looking at that KB article it doesn't seem like the same situation, as
> this is an API server cursor and the article discusses issues with Transact
> SQL cursors.
> Tony
>
> "Yogish" <yogishkamathg@icqmail.com> wrote in message
> news:D71D9B4D-1D43-4058-8A46-726531567601@microsoft.com...
> > Hi TonyL,
> >
> > Have you tried issuing DBCC FREEPROCCACHE command before running the
> > statement?
> >
> > Also, look at this KB article from MS
> > http://support.microsoft.com/kb/837957
> >
> > ---------
> > Thanks
> > Yogish
>
>
>

Relevant Pages

  • Re: Huge memory comsumption of ADODB Connection object
    ... unless the cursor is returning data to the client* I don't see ... I suppose this could cause some extra memory ... Is there a similar behavior in Sybase? ... Server was originally based on that rdbms, so there very well may be. ...
    (microsoft.public.data.ado)
  • Re: Cannot open Access table with ADO or BDE/ODBC
    ... How many records in this table, are there any blobs, do you ... Not tried with server side cursor. ... memory, since all this records will be fetched in memory. ...
    (borland.public.delphi.database.ado)
  • Re: Software Testing Tools
    ... you should design and redesign frequently while ... majority of its functionality implemented on the server. ... API for the client such that the UI is an entirely separate component - ... most efficient and reliable way to test the server is not with the GUI ...
    (comp.software.testing)
  • Re: VB connection to SQL server
    ... > the client machine begins to lose its relevance and accuracy as soon as it ... > aware that the data is probably out of date, a client sided cursor might ... > design minimises the possibility that records will have changed in the ... >> The Database server is in the office, and people use the Vb program from ...
    (microsoft.public.vb.database)
  • Re: Software Testing Tools
    ... a program thru one of its API interfaces is ideal, ... majority of its functionality implemented on the server. ... these defects are important to uncover. ... then you don't have "Design for Testing", and that's generally very bad, ...
    (comp.software.testing)