SQL Server - SQL-DMO Process Issue

Asked By Prasad on 21-Apr-10 08:54 AM
Hi All,

Inorder to monitor availability of sql server and sqlagent processes,
i have used the below script. Its functioning fine as we needed. But
end of the day i notice a lot of SQL-DMO process hanging in the
sysprocesses which subsequently cause the sql server to freeze.

I tried to search the blogs and sites, I have pasted the query
below...
Can somebody please help me on this. Thanks in Advance.

*************************************************************
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create proc [dbo].[usp_check_connection] (
@server varchar(100),
@linkedserver varchar(100))
as
--
-- This procedures is used to determine if a server (@server),
-- Can connect to a linked server (@linkedserver).  If
-- the server cannot connect to the linked server then it
-- sets the return code to 99.
--
-- Parameters:
--    @server       - This variable identifies the server that
--                    this sp will try to connect to, to verify the
--                    linked server connection. It is either the
--                    primary or backup monitoring server.
--    @linkedserver - This variable identifies the linked
--                    server that the sp tries to connect to.
--

---------------------
-- Begin Section A --
---------------------

-- Declare Local Variables
DECLARE @object int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
DECLARE @location varchar(100)
DECLARE @CMD nvarchar(1000)
DECLARE @objresults int
-- Set the location in the code for error processing
set @location = 'Create SQLServer Object'
-- Create a SQL Server object
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
-- check to see if object creation had an error
IF @hr <> 0 goto ERROR
-- set the Connection properties to use Window Authentication
EXEC @hr = sp_OASetProperty @object, 'LoginSecure', 'True'
-- Set the location in the code for error processing
set @location = 'Connect to Server - ' + rtrim(@server)
-- Connect to server
EXEC @hr = sp_OAMethod @object, 'Connect',Null,@server
-- Check to see if connection to server failed
IF @hr <> 0 goto ERROR
-------------------
-- End Section A --
-------------------

---------------------
-- Begin Section B --
---------------------
-- Set the location in the code for error processing
set @location = 'Execute TSQL to Linked Server - ' +
rtrim(@linkedserver)
-- Set the command to be executed on the linked server
set @cmd = 'ExecuteWithResults("select top 1 name From ' +
rtrim(@linkedserver) + '.master.dbo.sysobjects")'
-- execute command on linked server
EXEC @hr = sp_OAMethod @object,@cmd, @objResults OUT

-- check to see if process could connect to linked server
IF @hr <> 0 goto ERROR
-- Successfully connected, return without error code
RETURN

-------------------
-- End Section B --
-------------------

---------------------
-- Begin Section C --
---------------------
-- Error Handling Routine
ERROR:
IF @hr <> 0
BEGIN
-- Print the location of the error
Print @location
-- Print SQL-DMO error
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
END
-- Destroy the object.
BEGIN




Prasad replied to Erland Sommarskog on 22-Apr-10 02:33 AM
x

Hi,

Thank for your reply. I will explain a bit more on my requirement.
My requirement : Need to monitor all my mssql services. (sqlserver and
sqlserveragent). I have both sql2000 and sql2005 instances in my
environment.
Im running the above SP in my monitoring server, which is sql2005.

Can you pls suggest me how to handle such scenario.

thanks
Prasad replied to Prasad on 22-Apr-10 03:05 AM
Thank for your reply. I will explain a bit more on my requirement.
My requirement : Need to monitor all my mssql services. (sqlserver and
sqlserveragent). I have both sql2000 and sql2005 instances in my
environment.
Im running the above SP in my monitoring server, which is sql2005.
This SP will establish connection to all other sql servers and check
the availability of services.
Since this SP will be configured as a scheduled job. The SQL-DMO
process are keep on adding and causing the problem.

Can you pls suggest me how to handle such scenario.
Prasad replied to Erland Sommarskog on 22-Apr-10 06:58 AM
ded
not
echnol/sql/2005/downloads/books...
previousversions/books.mspx

Thanks a Lot. it Works.
Erland Sommarskog replied to Prasad on 21-Apr-10 05:38 PM
Prasad (calmsqurill@gmail.com) writes:

As far as I can tell, you do not destroy the object, if you connect
successfully.

Is this SQL 2000? Else there are better options.

Well, even on SQL 2000 there are other options. You could do an
xp_cmdshell to run OSQL that attempts to access the linked server.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Erland Sommarskog replied to Prasad on 22-Apr-10 05:23 AM
Prasad (calmsqurill@gmail.com) writes:


Have a look at sp_testlinkedserver in Books Online. This procedure was added
in SQL 2005, and should be the thing for you. If it for some reason does not
fit the bill, implement a solution using the CLR. The sp_OA stuff is
deprecated for very good reasons.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx