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
Click Here to download this Code.
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.
9 comments:
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
When we leave our bodies, it's because it's time to go home now, back to Source. gogo anime
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
Find out where and why things go wrong, and with their help get back on track? gogoanime
It is beyond exciting to communicate with our own animals, and with other animals you don't know and have never met. 123movie
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
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
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
Post a Comment