Sql-server – How to a raised alert fail to notify an operator via e-mail

This is more just a note on the philosophy rather than the technique.

If you did this, I would make sure to set some limits on it so that it only sent out emails when you were outside those limits.

For example, if you're CPU is spiking to 100% frequently, I would send out the email. However, if there's no problems with the CPU, I would explicitly, intentionally not send the email.

If you get a daily email from your database, you're going to start ignoring them after a while. But if you only get them when there's something that you think you should check for, you're more likely to look at the emails.

Also, I'd make those limits configurable (and possibly even make flags so that specific queries can be turned on or off).

Regarding your question of multiple selects inside a single email, it sounds like there are two options:

1. Get the results for each query and format them into a string that contains everything you want.

2. Merge all the different results into a single query and then set your @query to that. (Alternatively, sticking all the results into a temporary table and querying the temp table.)

It is possible, I should note, to stick a query in the @query as well as sticking the results of a second query in the @body (per here and here), but that only allows you two statements.

I would try option one inside of a stored procedure. The trick would be formatting the table into the string. However, it's the most likely to work correctly.

In management studio configure the database mail option under "Management" (both the account and the profile) and then use the stored procedure sp_send_dbmail in the msdb database.

http://msdn.microsoft.com/en-us/library/ms190307.aspx