Memory Leak When You Use SQL Server When I use OLE Automation Stored Procedures

Memory Leak When You Use SQL Server When I use OLE Automation Stored Procedures

Post by Andy Khokhlo » Thu, 28 Nov 2002 22:10:02



When I use OLE Automation Stored Procedures long time MS SQL Server report a
error:  Not enough storage is available to complete this operation.

How can I fix that problem?

 
 
 

Memory Leak When You Use SQL Server When I use OLE Automation Stored Procedures

Post by Andrew J. Kell » Thu, 28 Nov 2002 22:56:07


When you create the objects they need memory outside of the memory that SQL
Server has allocated.  Sounds like you have no upper memory limit set on sql
server and it is using up all the available memory at the time.  I would try
to add more ram if possible and set an upper memory limit on sql server to
leave room for the other processes like these if you have them running on
the same server.

--
Andrew J. Kelly
SQL Server MVP


Quote:> When I use OLE Automation Stored Procedures long time MS SQL Server report
a
> error:  Not enough storage is available to complete this operation.

> How can I fix that problem?


 
 
 

Memory Leak When You Use SQL Server When I use OLE Automation Stored Procedures

Post by Andy Khokhlo » Thu, 28 Nov 2002 23:28:00


Thanks. I'll try.



> When you create the objects they need memory outside of the memory that
SQL
> Server has allocated.  Sounds like you have no upper memory limit set on
sql
> server and it is using up all the available memory at the time.  I would
try
> to add more ram if possible and set an upper memory limit on sql server to
> leave room for the other processes like these if you have them running on
> the same server.

> --
> Andrew J. Kelly
> SQL Server MVP



> > When I use OLE Automation Stored Procedures long time MS SQL Server
report
> a
> > error:  Not enough storage is available to complete this operation.

> > How can I fix that problem?

 
 
 

Memory Leak When You Use SQL Server When I use OLE Automation Stored Procedures

Post by Neil Pik » Fri, 29 Nov 2002 06:24:05


 Andy,

 What version of SQL?  Try the latest SP.  Also see below for how to make the
mem available to OLE bigger

Quote:> When I use OLE Automation Stored Procedures long time MS SQL Server report a
> error:  Not enough storage is available to complete this operation.

> How can I fix that problem?

SQL Server 7.0 SP2 introduced a new g startup switch for the sqlservr
command-line utility:

-g memory_to_reserve

Specifies the number of megabytes (MB) of memory that SQL Server will leave
available for memory allocations within the SQL Server process, but outside the
SQL Server memory pool. The number specified must be an integer. The memory
pool is the area SQL Server uses for loading items such as extended stored
procedure .dll files, the OLE DB providers referenced by distributed queries,
and OLE Automation objects referenced in Transact-SQL statements. For more
information about SQL Server memory management, see "Memory Architecture" in
SQL Server Books Online for SQL Server 7.0.

The default value for this option is 128 MB, which is suitable for a wide range
of run-time environments. Using this option may help tune memory allocation,
but only when physical memory on the server exceeds 2 gigabytes (GB) for SQL
Server Desktop Edition or SQL Server Standard Edition, or 3 GB for SQL Server
Enterprise Edition. Configurations with less physical memory do not benefit
from using this option.

Use of this option may be appropriate for large memory configurations in which
the memory requirements of SQL Server are atypical and all of the memory in the
virtual address space of the SQL Server process is in use. Incorrect use of
this option can lead to conditions under which SQL Server may not start or may
encounter run-time errors.

You should use the default for the -g parameter unless you see the following
warning in the SQL Server error log:

Warning: Clearing procedure cache to free contiguous memory.
This message indicates that SQL Server may be attempting to free parts of the
SQL Server memory pool to find space for items such as extended stored
procedure .dll files or OLE Automation objects. In this case, consider
increasing the amount of memory reserved by the -g switch. Using a lower value
than the default increases the amount of memory available to the buffer pool
and thread stacks, which may in turn, provide some performance benefit to
memory-intensive workloads in systems that do not use many extended stored
procedures, distributed queries, or OLE Automation objects.

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 Reply here - no email
 SQL FAQ (484 entries) see
 http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
 (faqxxx.zip in lib 7)
 or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
 or www.sqlserverfaq.com
 or www.mssqlserver.com/faq

 
 
 

1. Memory Leak with Ole Automation in SQL Server when using seagate crystal reports

daily Iam printing seagate crystal reports using ole automation functions of
sql server. This sql server is our backup server. Once in a week i have to
reboot this server since every day the memory usage increases. There are no
major applications which are running on the backup sql server.

Please help me with any pointers or info or suggestions.

Thanks
Sunil.

2. Clarification of default CommandTimeout property

3. OLE Automation Problems - MEMORY HOG (Memory Leak?)

4. Backup/Restore Security Bug?

5. Help: Calling stored procedure from Impromptu using OLE Automation

6. ADO record selection list

7. Memory leak using OLE DB Provider ?

8. SQL Question

9. memory leak when using SQL Server with IIS5

10. Web server / ole automation using VFP5

11. extended stored procedures and T-SQL OLE automation API

12. SQL 7 OLE Automation Object via Stored Procedure

13. SQL OLE Automation stored Procedures