January 8, 2020 at 5:44 am
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
January 8, 2020 at 8:35 am
2 ways, store the contents of sys.databases and compare on each iteration any state changes, if the state changes, send an email saying that something has changed.
Use the built in alert system under SQL Agent, for the relevant message numbers for suspect mode, believe its 926 off the top of my head, but a check of your SQL Error Log or sys.messages will tell you which messages to look for. Then set the alert to send a mail.
January 8, 2020 at 8:54 am
I am able to send alert notification with below help
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply