?
Solved

a message to notify user when specific is offline

Posted on 2005-04-05
17
Medium Priority
?
536 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 

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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
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…

777 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