June 20, 2008 at 4:58 am
Is there a way for SQL2005 to send an email alert when a database becomes unavailable(offline, detached etc)
I already have Database mail working for other alerts for jobs etc, but I cant find how to raise alert when Db is unavailable.
thanks
Andy
June 20, 2008 at 8:11 am
It would be easy enough to set up a proc that did something like "select 1 from database.dbo.table" for each database on your server. Have it send an e-mail if there's an error. Schedule that in SQL Agent as a job with a high frequency. You won't get an immediate alert, but you could get one pretty darn quickly. (This kind of thing is refered to as a heartbeat check.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 20, 2008 at 8:24 am
check for errors [SQL Server], database engine in BOL and you can get a Error Number or something to setup the alerts..
Maninder
www.dbanation.com
June 20, 2008 at 8:47 am
GSquared (6/20/2008)
It would be easy enough to set up a proc that did something like "select 1 from database.dbo.table" for each database on your server. Have it send an e-mail if there's an error. Schedule that in SQL Agent as a job with a high frequency. You won't get an immediate alert, but you could get one pretty darn quickly. (This kind of thing is refered to as a heartbeat check.)
Wouldn't you take a performance hit doing something like this? It seems to me that passively watching for an error to be raised would not cost as much. Or am I oversimplifying that last statement?
June 20, 2008 at 11:49 am
jim.powers (6/20/2008)
GSquared (6/20/2008)
It would be easy enough to set up a proc that did something like "select 1 from database.dbo.table" for each database on your server. Have it send an e-mail if there's an error. Schedule that in SQL Agent as a job with a high frequency. You won't get an immediate alert, but you could get one pretty darn quickly. (This kind of thing is refered to as a heartbeat check.)Wouldn't you take a performance hit doing something like this? It seems to me that passively watching for an error to be raised would not cost as much. Or am I oversimplifying that last statement?
I'm talking about something like this:
use master
go
set nocount on
begin try
select 1
from mydatabase1.sys.all_objects
where name is null
select 1
from mydatabase2.sys.all_objects
where name is null
select 1
from mydatabase3.sys.all_objects
where name is null
end try
begin catch
exec msdb.dbo.sp_send_dbmail -- Send alert
@profile_name = 'Alerts',
@recipients = 'DBA@MyCompany.com',
@subject = error_message(),
@body = ''
end catch
Performance hit will be very, very minimal. A few CPU cycles per run. Run it every five minutes. Your server would have to be pretty much already over it's limits before it would notice this running in terms of resource-use.
(Note that I haven't tested the code in the catch block. You might have to set a variable to hold the error message, then call sp_send_dbmail. It might work as written.)
This will only catch the first missing database, if you have more than one. A slightly more sophisticated version could send one e-mail per missing database. A little beyond that, you could send one e-mail with a list of them in the body or as an attachment.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 20, 2008 at 3:57 pm
Would create a server level DDL Trigger be an option?
DDL_SERVER_LEVEL_EVENTS
http://msdn.microsoft.com/en-us/library/ms186582.aspx
I've created a server level trigger to catch people adding Logins. I catch it and then send myself/group a database mail. 😛
June 23, 2008 at 2:19 am
I have been looking at the sysdatabases table and it looks like when a database goes offline the version field changes from 611 to NULL, if it is detached the the database is removed from the table.
Would this be a reliable way of detecting if a db is unavailable
thanks
June 24, 2008 at 7:56 am
Try use sys.databases instead of sys.sysdatabases. sys.databases gives you so much more information.
check the state and statedesc columns in sys.databases.
However, this will not help you with figuring out databases that are detached.
I think you should seriously take a look at server level DDL triggers.
June 24, 2008 at 9:56 am
You can have a job that polls the sysdatabases table on whatever box you are interested, pop this into a table and the next time you poll see if there are differences, either new databases or deleted\detatached, from there you can then create an alert.
Andrew
June 25, 2008 at 2:00 pm
This query will tell you the status (ONLINE, OFFLINE, SUSPECT, etc...) of all the databases on your server, however it will not tell you if the database has been dropped or detached.
SELECT [name], state_desc FROM sys.databases
For a dropped or detached database I would do a count on SELECT COUNT([name]) FROM sys.databases and store the value in a temp table. Run the query again and if the number is not the same send you a message.
Thanks
June 25, 2008 at 2:31 pm
Hi Andy,
MOM server e-mails the status of database when it is suspended or dropped.
Baby
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply