Out of memory problem

We have SQL Server 2000 on a Win2k Server with 2GB of Ram. I have set the server to Dynamically configure SQL Server memory. Currently the maximum memory is set at 1.8GB. Over time SQL Server occupies most of the memory leaving about 10 MB available memory. I read that this is ok if the available memory gets down as low and that SQL Server manages the memory fine from one of the microsoft articles. Well, it keeps running this way for some time.. 1 to 2 days and then users start losing connections, cannot make new connections and a stored procedure which creates a OLE object starts failing with this message given at the bottom of the message. Note the OLE object is created and run within the SQL Server memory and not outside of it
I had set the max memory for SQL Server as 2GB intially. I reduced this to 1.8GB and yet after couple of days of functioning ok, the server started giving these errors. So every time this happens we have to stop the SQL Server service and start again. Can anyone let me know why this is happening and what the SQL Server settings should be to prevent this from happening
Server: Msg 14, Level 16, State 1, Line
Not enough storage is available to complete this operation
Server: Msg 10019, Level 16, State 1, Line
sp_OACreate has not yet been called successfully for this command batch
OLE Automation Error Informatio
HRESULT: 0x8000400
Thanks
VasBased on the smptoms and this comment:
<<
Note the OLE object is created and run within the SQL Server memory and not
outside of it.
I'd be willing to bet an awful lot of money that there is a) a memory leak
in your COM object or b) you've hit a mem leak error in the MDAC stack (or
somewhere).
I'd make sure I'm on all the latest service packs for SQL and any software
that you could be calling from the COM object.
Also... you might be able to a) prove that the leak is related to your COM
object and b) work around the problem for a short period of time by running
the sp_OA procs outside the SQL memory space.
But I'd troubleshoot this as a memory leak error for now...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Vasu Venkat" <anonymous@.discussions.microsoft.com> wrote in message
news:7D8CD8F5-918D-41DB-974A-3AA3B6F3026A@.microsoft.com...
> We have SQL Server 2000 on a Win2k Server with 2GB of Ram. I have set the
server to Dynamically configure SQL Server memory. Currently the maximum
memory is set at 1.8GB. Over time SQL Server occupies most of the memory
leaving about 10 MB available memory. I read that this is ok if the
available memory gets down as low and that SQL Server manages the memory
fine from one of the microsoft articles. Well, it keeps running this way for
some time.. 1 to 2 days and then users start losing connections, cannot make
new connections and a stored procedure which creates a OLE object starts
failing with this message given at the bottom of the message. Note the OLE
object is created and run within the SQL Server memory and not outside of
it.
> I had set the max memory for SQL Server as 2GB intially. I reduced this to
1.8GB and yet after couple of days of functioning ok, the server started
giving these errors. So every time this happens we have to stop the SQL
Server service and start again. Can anyone let me know why this is happening
and what the SQL Server settings should be to prevent this from happening.
> Server: Msg 14, Level 16, State 1, Line 0
> Not enough storage is available to complete this operation.
> Server: Msg 10019, Level 16, State 1, Line 0
> sp_OACreate has not yet been called successfully for this command batch.
> OLE Automation Error Information
> HRESULT: 0x80004005
> Thanks,
> Vasu
>