Get notified when the database status change

Last week there was a question in MSDN Forums “How to get alerted when a database status change”. This piece of code can be used in a job to notify DBA’s about the status change of a database. The job can be scheduled say once every minute which checks the status of a database and alert you if it is anything but Online.

The sample code will send an email if the status of the “Model” database changes to anything other than Online. The code can send email to receipents you want to be notified.

declare @state varchar(10)
declare @Database_Name varchar(100)
declare @email varchar(100)
set @state = ''
set @email = ''
set @Database_Name = 'Model'

select @state = 
case 
When a.state = 0 then 'Online'
when a.state = 1 then 'Restoring'
when a.state = 2 then 'Recovery_Pending'
when a.state = 4 then 'Suspect'
when a.state = 5 then 'Emergency'
when a.state = 6 then 'Offline'
when a.state = 7 then 'Copying - SQL Azure'
when a.state = 10 then 'Ofline_Secondary - SQL Azure'
end
from sys.databases a where name = @Database_Name

if @state <> 'Online'
begin
set @email = 'The database ' + @Database_Name + ' is currently : ' + @state

EXEC msdb.dbo.sp_send_dbmail
	@profile_name = 'Profile_name_of_DBMAIL',
    @recipients = 'Recipient EmailID',
        @body = @email,
    @subject = 'Database Status' ;
end
 

3 thoughts on “Get notified when the database status change

  1. Thank you for the query. Where do we enter the email address of the receipients email address?

    For example:

    @recipients= ‘abc123’,
    @body = @gmail.com

    • You enter the recipients email address in the @recipients …ie @recipients = abc123@gmail.com….. The @body =@email is not to be changed.. if you look in the code @email is the name of the variable which describes the body of the email… Eg: “The database abc is currently : Offline”.. Hope it is clear

  2. I tried this code. it runs successfully, but it doesn’t send email to recipients.
    I copied the code. just change the email profile to main and add recipient. then created a job to run every 3 mins.
    is there anything that i am not doing right?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s