[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

a message to notify user when specific is offline

Posted on 2005-04-05
17
Medium Priority
?
543 Views
Last Modified: 2008-02-07
The DB server is SQL server 2000.

I want to setup a message to notify user when specific is offline. How to do that?
0
Comment
Question by:DataField
  • 8
  • 6
  • 2
  • +1
17 Comments
 
LVL 2

Expert Comment

by:curlypinhead
ID: 13709404
when specific what is offline?
0
 

Author Comment

by:DataField
ID: 13709445
specific DB.

0
 
LVL 2

Expert Comment

by:curlypinhead
ID: 13709633
is the user on a webpage?  what kind of message are you talking about?

if the user is on a webpage, you could use:

try{

connect to db

}catch(exception){

print error message

}

or something of that sort, depending on what language you are using.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

Author Comment

by:DataField
ID: 13709957
I want to use SQL server internal alert, not sure how to set it up?
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13710238
So you want SQL Server to monitor the status of its databases and alert users when/if they do what?
0
 

Author Comment

by:DataField
ID: 13710277
Right, the purpose is to monitor the DB status. When the DB is offline for some reason I could receive an alert message.

0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 13710407
One way would be to create a separate app that makes/closes a connection to your database every minute, and maybe run a simple query.  If it performs without errors, do nothing.  If an error is generated, create an alert message (email to a pager, email to a couple of people, etc.).

Also, place it on startup of a server to insure that it's running 24x7-ish, assuming the Server people like you and are willing to do this for you.

We did this with VB all the time to monitor various FTP apps.
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13710447
SELECT DATABASEPROPERTYEX('Northwind', 'Status')


This would return a variety of possibilities:
ONLINE = database is available for query
OFFLINE = database was explicitly taken offline
RESTORING = database is being restored
RECOVERING = database is recovering and not yet ready for queries
SUSPECT = database cannot be recovered

Build your alerting around this...
0
 
LVL 2

Expert Comment

by:curlypinhead
ID: 13710489
I saw your other post at:  http://www.experts-exchange.com/Databases/Q_21352204.html

if the error code isn't working, its probably because the error is not firing.  check the errorlog in the /log folder of the sql server installation.  See if an error is generated.  If an error is not generated, an alternate method would be to use an alert of type 'sql server performance condition alert'.

Set the object to 'SQLServer: General Statistics'

Set the counter to 'User Connections'

Set the alert if counter to 'Becomes equal to'

Set the value to 0.

This will only work if you regularly have people connected to the database.  if nobody is connected, something is probably wrong with your database connection, and you will get an alert fired.  Your other post said that the email is working so I won't go into that.

curlypinhead
0
 

Author Comment

by:DataField
ID: 13710870
Thanks for you guys' reply.

You are right, I try to excute 'user DB001' to tirgger the error.

I check the sql server log, I can see the error was fired.
************************
2005-04-05 12:00:00.81 spid58    Error: 942, Severity: 14, State: 4
2005-04-05 12:00:00.81 spid58    Database 'DB001' cannot be opened because it is offline..
************************

But from 'Enterprise Manager --> Management --> Alters', the alert status is 'Never Occurred', I am sure the alert was created and setup correctly (Type/Error Number/DB Name/Enable). It just confuse me, why????
0
 
LVL 2

Expert Comment

by:curlypinhead
ID: 13712163
My guess is that you have not started sql server agent.

right click on sql server agent and click on start.

This should fix it if it is not already started.

curlypinhead
0
 

Author Comment

by:DataField
ID: 13712411
the sql server agent is started.
0
 
LVL 2

Expert Comment

by:curlypinhead
ID: 13712417
i tried doing the same thing on my machine and it worked.  ARe you sure you have the right error number in the alert (the same one that is in the event log)?

0
 

Author Comment

by:DataField
ID: 13712523
Really? Let me list the detail how I setup and test the ALERT:

1. Create a new alert from 'Enterprise Manager --> Management --> Alert',

Name: Test DB Offline
Type: Sql Server Event Alert
Enable: checked
Error Number: 942
Database Name: DB001
Error Message Contain this text: offline

2. I take the DB001 offline, and excute 'use DB001'.

3. I can see the error from sql server event log.

4. But status for 'Test DB Offline' is 'Never Occurred' even after refreshing the view.
0
 

Author Comment

by:DataField
ID: 13712536
Actually I tried it on three different computers, my working station and two servers.

It didn't work on all of them.
0
 
LVL 2

Accepted Solution

by:
curlypinhead earned 1000 total points
ID: 13712619
it looks like because you use DB001 in the database name field, it isn't getting fired.  If you use all databases it will fire.

I think that is because you are using the 'use db001' command and the error is getting fired while using a different database... if that makes any sense.

0
 

Author Comment

by:DataField
ID: 13712643
I use 'all database' in the alert. It works!!! Thanks a lot.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question