SQL-DMO Process Issue
in [Using MS SQL Server]
|
From: Prasad on 22 Apr 2010 02:33 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 22 Apr 2010 03:05 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 22 Apr 2010 05:23 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 22 Apr 2010 06:58 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.
|
Pages: 1 Prev: SQL Joins Next: ANN: New version of Database Workbench, the multi-DBMS IDE now available! |