Re: SQL-DMO Process Issue

#1

Prasad (calmsqurill (AT) gmail (DOT) com) writes:

Quote:

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.

As far as I can tell, you don't 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 (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx

#2

On Apr 22, 5:38*am, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:

Quote:

Prasad (calmsqur... (AT) gmail (DOT) com) writes:
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.

As far as I can tell, you don't 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, esq... (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx

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

#3

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.

#4

Prasad (calmsqurill (AT) gmail (DOT) com) writes:

Quote:

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.

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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#5

On Apr 22, 5:23*pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:

Quote:

Prasad (calmsqur... (AT) gmail (DOT) com) writes:
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.

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, esq... (AT) sommarskog (DOT) se

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

Thanks a Lot. it Works.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: SQL-DMO Process Issue

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Re: SQL-DMO Process Issue - 04-21-2010 , 04:38 PM

Re: SQL-DMO Process Issue - 04-22-2010 , 01:33 AM

Re: SQL-DMO Process Issue - 04-22-2010 , 02:05 AM

Re: SQL-DMO Process Issue - 04-22-2010 , 04:23 AM

Re: SQL-DMO Process Issue - 04-22-2010 , 05:58 AM