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.
@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.