Db mail -Mirroring alert code

SQL Server Administration
1

Hi,

I am new to the SQL coding. Need help in configuring the mirroring alert when the db will go to suspect mode.
I configured dbmailprofile and succesfully i am able to send the mail through sql server .
now for alert i given the below step in job.

exec msdb.dbo.sp_send_dbmail
@profile_name='newprofile1',
@recipients='abc@xyz.com',
@query='select name,state_desc from sys.databases where state in (0,1,2,3,4,5,6)'
@subject='db status alert',
@body='status of the db'

I required the code in such a way that alert should generate when ever the database will go to suspect mode.
As of now i configured in such a way that its throwing mail with current db status.
But need mail only when db will go to suspect mode.

i configured the job schedule for every 15 min. instead of giving mail for every 15 min , i should get mail only database will go to suspect mode.

Please help me to modify the code. Its little urgent.

Thanks,
Jo

2

I am able to send alert with below code

3

Is this an AlwaysOn Availability Group - or are you just using database mirroring? You can setup SQL Server Agent alerts for either but which alerts you define depends on what has been implemented.

Note: you also have to setup and configure the agent to use database mail.