Send e-mail notification when a database is not online | SansSQL

Saturday, January 22, 2011

Send e-mail notification when a database is not online

Checking the databases availability is one of the major task of a DBA which has to be performed  at regular intervals to make sure that the critical live databases are always online and fix them immediately if they are not online. This can be done in "n" number of ways and here is one such method.
The method that i am going to discuss below makes use of "Database Mail" option to send e-mails to the intended recipients when the process finds the status of the database which is not online.
If you have still not configured the "Database Mail" Option then Click Here to get the steps to configure "Database Mail" before you continue with this.

After the "Database Mail" Option is configured all you have to do is to create a job with the below code and schedule it to run every five minutes or every one minute depending on the criticality of the databases that you are going to monitor.

Use this Code if you wish to receive the e-mail in HTML format:
if(select count(*) from sys.databases where state_desc<>'Online')>0
Begin

DECLARE @table  NVARCHAR(MAX) ;

SET @table =
    N'<H1>Offline Databases Report</H1>' +
    N'<table border="1">' +
    N'<tr><th>Database Name</th><th>Database Status</th></tr>' +
    CAST ( ( Select td=name, '',td=state_desc from sys.databases where state_desc<>'Online'
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) )    +
    N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail @profile_name='ProfileName', --Change to your Profile Name
      @recipients='email@domain.com;email1@domain.com', --Put the email address of those who want to receive the e-mail
    @subject = 'Offline Databases Report',
    @body = @table,
    @body_format = 'HTML' ;

END
Else Print 'All Databases are Online'

Click Here to download this Code.



Use this Code if you wish to receive the e-mail in Plain Text format:
if(select count(*) from sys.databases where state_desc<>'Online')>0
Begin

EXEC msdb.dbo.sp_send_dbmail @profile_name='ProfileName', --Change to your Profile Name
      @recipients='email@domain.com;email1@domain.com', --Put the email address of those who want to receive the e-mail
    @subject = 'Offline Databases Report',
    @Query='Select rtrim(ltrim(name)) as DatabaseName,rtrim(ltrim(state_desc)) as CurrentDatabaseStatus from sys.databases where state_desc<>''Online'''
    
END
Else Print 'All Databases are Online'

Click Here to download this Code.

11 comments:

tike mik said...

Michael's reasoning to pull over at the "previous" exit, played right into, the agency designs to dupe the "guilty" one (holding) to such a precise action.

kissmanga.com

tike mik said...

When we leave our bodies, it's because it's time to go home now, back to Source. gogo anime

tike mik said...

Unless their new guardian knows how to deal successfully with the behaviors or are able to help them heal, then often they are returned to the system like orphans in our foster care system. Over and over again. 9 anime

tike mik said...

Find out where and why things go wrong, and with their help get back on track? gogoanime

tike mik said...

It is beyond exciting to communicate with our own animals, and with other animals you don't know and have never met. 123movie

Huongkvb said...

Mua vé máy bay tại Aivivu, tham khảo

vé máy bay đi Mỹ bao nhiêu

đăng ký về việt nam ở mỹ

từ canada về việt nam quá cảnh ở đâu

mua vé từ nhật về việt nam

Lịch bay từ Hàn Quốc về Việt Nam tháng 7

Vé máy bay từ Đài Loan về VN

Chi phí cho chuyên gia nước ngoài

Lucky Patcher said...
This comment has been removed by the author.
rajesh said...

HI, i have executed the code and found that error first line
if(select count(*) from sys.databases where state_desc<>'Online')>0
Please let me know the exact code

Willeum Bruch said...

From the tons of comments on your articles, I guess I am not the only one having all the enjoyment here! keep up the good workbuy email lists

ken said...


A content worth to read because I could see the effort you put into this article. Thank you very much have share this.PRTOTO

AxelPRTOTO said...

Wonderful blog with a very good written process and very easy to digest and very enjoyable. PRTOTO

Post a Comment

Ads