Memory Leak with sp_OACreate and EXECUTE AS OWNER
Hi,
We have been encountering an "Out of Memory" issue with SQL Server 2005, and have tracked it down to the usage of sp_OACreate and sp_OADestroy in a stored procedure. We discovered that logging in and running the procedure as sa, without "EXECUTE AS OWNER" in the procedure worked fine without any memory leak, but the including "EXECUTE AS OWNER" caused the server memory usage to increase, and never be released.
Has anyone else encountered this issue, or have they a solution?
Cheers,
Chris
[540 byte] By [
ChrisMcV] at [2007-12-27]
?
I was able to reproduce this, as well, in SQL Server 2005 RTM (9.0.1399). I unfortunately don't have an SP1 installation
handy to test with -- are you running SP1? If not, you should try that
first. If you are already running SP1, you should file a bug on
connect.microsoft.com about it, and then post back here with the URL so that I
can vote for it!
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
Hi,
We have been encountering an "Out of Memory" issue with SQL Server 2005,
and have tracked it down to the usage of sp_OACreate and sp_OADestroy in a
stored procedure. We discovered that logging in and running the procedure as
sa, without "EXECUTE AS OWNER" in the procedure worked fine without any memory
leak, but the including "EXECUTE AS OWNER" caused the server memory usage to
increase, and never be released.
Has anyone else encountered this
issue, or have they a solution?
Cheers,
Chris
Hi
I observed the same behavior with my program SQL 2005 express grabbing memory continuously and not releasing. I had a program which posts 1 million records to database and I can see the memory grows from 27MB to 400 MB. I can limit the memory of server using
EXEC sys.sp_configure N'max server memory (MB)', N'50'
GO
RECONFIGURE WITH OVERRIDE
GO
After applying above command to the database the memory size is restricted to ~ 84MB for the same test.
By default configuration for SQL 2005 express was to use 2147483647 MB (too much) memory which causes SQL to grab the new memory and doesn't reuse the existing one.
