Database suspect

[sqlturbo]

Recently, one of our database went offline and EM showed it as Suspect. We got to know about it only when the users couldn't logon to the site. If something like that happens in the future, I'd like to trap any error that SQL Server 2000 would throw, and set off a mail to the administrators informing them that that's what's wrong. One of the solutions that I am trying to implement will use CDOSYS (The SQL box runs on a cluster and SQL Mail behaves erratically on the cluster. So, the use for CDOSYS)to notify the administrator via a mail.

Firstly, How do I 'trap' or 'read' any error that SQL Server 2000 would spit out( I don't have much experience using SQL OA procedures). And secondly, are there other ways to notify the administrators about a database going offline? The SQL mail notification hasn't worked for us as we run SQL Server on a cluster.

Any ideas?

Thanks

 

[ecobb]

Have you checked under the SQL Server Agent in Enterprise manager? You can create an alert notification there, and then create an operator and specify how to deliver the message (email, pager, etc...) Whenever that specific problem happens, it will notifiy the designated operator for that alert. You can even set a scedule as to who to alert and when (weekend pager schedules and so forth).

Hope this helps.

 

[sqlturbo]

Like I mentioned in the problem, we run the SQL Server on a cluster and the mail don't get sent, if they need to be sent, after the failover. MS acknowledges this to be a problem. (

http://support.microsoft.com/default.aspx?scid=kb;en-us;298723).

And that is the reason why I am trying to implement something using CDOSYS. CDOSYS does not use the mail profile on SQL server to send out the mails and therfore would work on the cluster.

 

[tlbroadbent]

In Enterprise Manager, Create an Alert for error 926, "Database '%.*ls' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information." Have the SQLAgent send a message to the appropriate people when the Alert is fired. SQLAgent does not use SQL Mail. It has its own mail process but can use the same Exchange mailbox. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[sqlturbo]

Mcuh thanks for the solution, Terry.

 

[sqlturbo]

Much thanks for the solution, Terry.