Monitoring SQL Server 2000


I need to write a VB.NET class that checks to see if SQL Server is running on the machine. This class will be mul-threaded and check every 30 seconds ( approx ) . The easiest way of doing this i suppose is to repeatedly attempt to open a connection and then close it.  Or would it be better to open a single connection and then check the stage of the connection every 30 seconds ?

Is there a more eligent way of doing this.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
WHY do you want to write an application that checks if SQL Server is running?
crestsolutionsAuthor Commented:

Just a simple monitor class for a bigger app which will be sending off emails, alerting users etc if SQL stops running. I'd like to know as soon as possible if SQL is not running, not find out when I attempt to connect and insert data .

Failed to mention in the above  - SQL Server will not be running on the same machine as the app.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Actually, reopening the connection should be better (releasing the resources asap)

Now, you have several states of the SQL Server:
1) service stopped
2) service started, but database missing/offline
3) database online, but login/user/password incorrect
4) login works, but queries would fail because of (wrong) security settings
5) security settings all ok, but data modification (insert/update/delete) would fail because
* transaction log file(s) full and are not allowed to grow
* data file(s) full and are not allowed to grow
* disk(s) full

It does not matter where the app and the sql are, just a good connection string needs to be supplied (assuming ado installed)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

crestsolutionsAuthor Commented:

I've codes a simply method to run in a thread. The method creates a new SQLConnection and opens it. I then check the state before return true of false.

The problem is if i run my app the connection will open as expected. If stop sql server service the connection still opens.

The only time the connection will not open is when i stop the sql server service before running my app.

any ideas ?

Bob LearnedCommented:
How many users would you have at most?  Open/closing a connection every 30 seconds could produce a condition where you run out of connections quickly if you have a lot of users at one time.

crestsolutionsAuthor Commented:
There will only be 4 users so connections will not be an issue.

Got around that connection issue above by actually executing an empty stored procedure. The sqlCommand object throws an exception is the databsae is stopped even though the connection.state says it is open.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.