From: Prasad on
On Apr 22, 5:38 am, Erland Sommarskog <esq...(a)sommarskog.se> wrote:
> Prasad (calmsqur...(a)gmail.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...(a)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

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
From: Prasad on
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.
From: Erland Sommarskog on
Prasad (calmsqurill(a)gmail.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, esquel(a)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
From: Prasad on
On Apr 22, 5:23 pm, Erland Sommarskog <esq...(a)sommarskog.se> wrote:
> Prasad (calmsqur...(a)gmail.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...(a)sommarskog.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.