Hi,
We have a stored procedure that we call very frequently
that polls servers to see if they are available. This is
used from various other stored procedures that may then
use an available SQL Server or try another one.
We have implemented the stored procedure such that it uses
the SP_OA routines to perform a SQL-DMO connect. This
works very well and allows us to catch the 'server
unavailable' error in stored procedure code and branch.
Unfortunataly, if the SQL Server that issues the connect
has not been restarted for a few says we get the following
message back from the connect method (from
sp_OAGetErrorInfo):
0x80045900 Microsoft SQL-DMO [SQL-DMO]Out of
memory.
I have included the code fragment below we are using. Has
anyone any thoughts, please, on where our memory leak is
happening. All our SQL Servers are SP2 currently.
Many thanks, Ken
---
CREATE PROCEDURE xxx @.ServerName VARCHAR(20),
@.LoginTimeout INT = 10
AS
SET NOCOUNT ON
DECLARE @.iRet int
DECLARE @.object int
DECLARE @.hr int
DECLARE @.src varchar(255), @.desc varchar(255)
-- @.iRet - return value of procedures
-- @.object - object reference
-- @.hr - handle
-- @.src - source of error message
-- @.desc - description of error message
-- Set up parameters/variables
SELECT @.iRet = 0, @.hr = 0, @.object = 0
-- create SQLDMO SQLServer object
EXEC @.hr = sp_OACreate 'SQLDMO.SQLServer', @.object OUT
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT,
@.desc OUT
SELECT hr=convert(varbinary(4),@.hr),
Source=@.src, Description=@.desc
RETURN (2)
END
-- Set the properties
-- Set the LoginTimeout property.
EXEC @.hr = sp_OASetProperty @.object, 'LoginTimeout',
@.LoginTimeout
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
Description=@.desc
EXEC @.hr = sp_OADestroy @.object
RETURN (3)
END
-- Set the integrated login property.
EXEC @.hr = sp_OASetProperty @.object,'LoginSecure',1
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
Description=@.desc
EXEC @.hr = sp_OADestroy @.object
RETURN (4)
END
-- attempt to connect to server
EXEC @.hr = sp_OAMethod @.object, 'Connect', NULL,
@.ServerName
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
Description=@.desc
SELECT @.iRet = convert(varbinary(4),@.hr)
EXEC @.hr = sp_OADestroy @.object
RETURN (CASE @.iRet WHEN 0x80020009 THEN 2 ELSE 1
END) -- 2 = 'SQL Server does not exist or access denied',
1 = misc. error
END
-- Disconnect
EXEC @.hr = sp_OAMethod @.object, 'DisConnect'
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
OUT
SELECT hr=convert(varbinary(4),@.hr),
Source=@.src, Description=@.desc
EXEC @.hr = sp_OADestroy @.object
RETURN (5)
END
-- Destroy object
EXEC @.hr = sp_OADestroy @.object
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
Description=@.desc
RETURN (6)
END
-- success
RETURN (0)
GOIt's difficult to trace memory leaks like this. I can't help with the DMO
specific troubleshooting, but... I would encourage to you to review the fix
list for SP3a. I remember reading about some memory leak issues, don't
remember what they were... but you don't want to waste time on a fix in the
latest SP.
Also... do you have an ability to either a) rollout SP3 (which you really
should anyway for security purposes) or b) at least roll it out in a QA
envrionment where you can test this memory leak against Sp3. That might save
you a lot of time...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Ken Eng" <keneng23@.hotmail.com> wrote in message
news:0d5001c36e33$069fcfb0$a301280a@.phx.gbl...
> Hi,
> We have a stored procedure that we call very frequently
> that polls servers to see if they are available. This is
> used from various other stored procedures that may then
> use an available SQL Server or try another one.
> We have implemented the stored procedure such that it uses
> the SP_OA routines to perform a SQL-DMO connect. This
> works very well and allows us to catch the 'server
> unavailable' error in stored procedure code and branch.
> Unfortunataly, if the SQL Server that issues the connect
> has not been restarted for a few says we get the following
> message back from the connect method (from
> sp_OAGetErrorInfo):
> 0x80045900 Microsoft SQL-DMO [SQL-DMO]Out of
> memory.
> I have included the code fragment below we are using. Has
> anyone any thoughts, please, on where our memory leak is
> happening. All our SQL Servers are SP2 currently.
>
> Many thanks, Ken
> ---
> CREATE PROCEDURE xxx @.ServerName VARCHAR(20),
> @.LoginTimeout INT = 10
> AS
> SET NOCOUNT ON
>
> DECLARE @.iRet int
> DECLARE @.object int
> DECLARE @.hr int
> DECLARE @.src varchar(255), @.desc varchar(255)
> -- @.iRet - return value of procedures
> -- @.object - object reference
> -- @.hr - handle
> -- @.src - source of error message
> -- @.desc - description of error message
> -- Set up parameters/variables
> SELECT @.iRet = 0, @.hr = 0, @.object = 0
> -- create SQLDMO SQLServer object
> EXEC @.hr = sp_OACreate 'SQLDMO.SQLServer', @.object OUT
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT,
> @.desc OUT
> SELECT hr=convert(varbinary(4),@.hr),
> Source=@.src, Description=@.desc
> RETURN (2)
> END
> -- Set the properties
> -- Set the LoginTimeout property.
> EXEC @.hr = sp_OASetProperty @.object, 'LoginTimeout',
> @.LoginTimeout
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
> OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
> Description=@.desc
> EXEC @.hr = sp_OADestroy @.object
> RETURN (3)
> END
>
> -- Set the integrated login property.
> EXEC @.hr = sp_OASetProperty @.object,'LoginSecure',1
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
> OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
> Description=@.desc
> EXEC @.hr = sp_OADestroy @.object
> RETURN (4)
> END
> -- attempt to connect to server
> EXEC @.hr = sp_OAMethod @.object, 'Connect', NULL,
> @.ServerName
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
> OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
> Description=@.desc
> SELECT @.iRet = convert(varbinary(4),@.hr)
> EXEC @.hr = sp_OADestroy @.object
> RETURN (CASE @.iRet WHEN 0x80020009 THEN 2 ELSE 1
> END) -- 2 = 'SQL Server does not exist or access denied',
> 1 = misc. error
> END
> -- Disconnect
> EXEC @.hr = sp_OAMethod @.object, 'DisConnect'
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
> OUT
> SELECT hr=convert(varbinary(4),@.hr),
> Source=@.src, Description=@.desc
> EXEC @.hr = sp_OADestroy @.object
> RETURN (5)
> END
> -- Destroy object
> EXEC @.hr = sp_OADestroy @.object
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
> OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
> Description=@.desc
> RETURN (6)
> END
> -- success
> RETURN (0)
> GO
>
>|||Thanks Brian,
I will move to SP3 to take this out of the equation.
Cheers
Ken
>--Original Message--
>It's difficult to trace memory leaks like this. I can't
help with the DMO
>specific troubleshooting, but... I would encourage to you
to review the fix
>list for SP3a. I remember reading about some memory leak
issues, don't
>remember what they were... but you don't want to waste
time on a fix in the
>latest SP.
>Also... do you have an ability to either a) rollout SP3
(which you really
>should anyway for security purposes) or b) at least roll
it out in a QA
>envrionment where you can test this memory leak against
Sp3. That might save
>you a lot of time...
>--
>Brian Moran
>Principal Mentor
>Solid Quality Learning
>SQL Server MVP
>http://www.solidqualitylearning.com
>
>"Ken Eng" <keneng23@.hotmail.com> wrote in message
>news:0d5001c36e33$069fcfb0$a301280a@.phx.gbl...
>> Hi,
>> We have a stored procedure that we call very frequently
>> that polls servers to see if they are available. This is
>> used from various other stored procedures that may then
>> use an available SQL Server or try another one.
>> We have implemented the stored procedure such that it
uses
>> the SP_OA routines to perform a SQL-DMO connect. This
>> works very well and allows us to catch the 'server
>> unavailable' error in stored procedure code and branch.
>> Unfortunataly, if the SQL Server that issues the connect
>> has not been restarted for a few says we get the
following
>> message back from the connect method (from
>> sp_OAGetErrorInfo):
>> 0x80045900 Microsoft SQL-DMO [SQL-DMO]Out of
>> memory.
>> I have included the code fragment below we are using.
Has
>> anyone any thoughts, please, on where our memory leak is
>> happening. All our SQL Servers are SP2 currently.
>>
>> Many thanks, Ken
>> ---
>> CREATE PROCEDURE xxx @.ServerName VARCHAR(20),
>> @.LoginTimeout INT = 10
>> AS
>> SET NOCOUNT ON
>>
>> DECLARE @.iRet int
>> DECLARE @.object int
>> DECLARE @.hr int
>> DECLARE @.src varchar(255), @.desc varchar(255)
>> -- @.iRet - return value of procedures
>> -- @.object - object reference
>> -- @.hr - handle
>> -- @.src - source of error message
>> -- @.desc - description of error message
>> -- Set up parameters/variables
>> SELECT @.iRet = 0, @.hr = 0, @.object = 0
>> -- create SQLDMO SQLServer object
>> EXEC @.hr = sp_OACreate 'SQLDMO.SQLServer', @.object OUT
>> IF @.hr <> 0
>> BEGIN
>> EXEC sp_OAGetErrorInfo @.object, @.src OUT,
>> @.desc OUT
>> SELECT hr=convert(varbinary(4),@.hr),
>> Source=@.src, Description=@.desc
>> RETURN (2)
>> END
>> -- Set the properties
>> -- Set the LoginTimeout property.
>> EXEC @.hr = sp_OASetProperty @.object, 'LoginTimeout',
>> @.LoginTimeout
>> IF @.hr <> 0
>> BEGIN
>> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
>> OUT
>> SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
>> Description=@.desc
>> EXEC @.hr = sp_OADestroy @.object
>> RETURN (3)
>> END
>>
>> -- Set the integrated login property.
>> EXEC @.hr = sp_OASetProperty @.object,'LoginSecure',1
>> IF @.hr <> 0
>> BEGIN
>> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
>> OUT
>> SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
>> Description=@.desc
>> EXEC @.hr = sp_OADestroy @.object
>> RETURN (4)
>> END
>> -- attempt to connect to server
>> EXEC @.hr = sp_OAMethod @.object, 'Connect', NULL,
>> @.ServerName
>> IF @.hr <> 0
>> BEGIN
>> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
>> OUT
>> SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
>> Description=@.desc
>> SELECT @.iRet = convert(varbinary(4),@.hr)
>> EXEC @.hr = sp_OADestroy @.object
>> RETURN (CASE @.iRet WHEN 0x80020009 THEN 2 ELSE 1
>> END) -- 2 = 'SQL Server does not exist or access
denied',
>> 1 = misc. error
>> END
>> -- Disconnect
>> EXEC @.hr = sp_OAMethod @.object, 'DisConnect'
>> IF @.hr <> 0
>> BEGIN
>> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
>> OUT
>> SELECT hr=convert(varbinary(4),@.hr),
>> Source=@.src, Description=@.desc
>> EXEC @.hr = sp_OADestroy @.object
>> RETURN (5)
>> END
>> -- Destroy object
>> EXEC @.hr = sp_OADestroy @.object
>> IF @.hr <> 0
>> BEGIN
>> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc
>> OUT
>> SELECT hr=convert(varbinary(4),@.hr), Source=@.src,
>> Description=@.desc
>> RETURN (6)
>> END
>> -- success
>> RETURN (0)
>> GO
>>
>
>.
>
