Sql-server – Health checking via Database Mail in SQL Server

sql-server-2005sql-server-2008

I have a few SQL scripts that I would like to run every day on my SQL Server(s). How can I query multiple times and have the output similar to the following:

Health check for <server>

Query Title 1:

<query results 1>


Query Title 2:

<query results 2>

I am checking things like last good back up, uptime etc so they are not related to each other. I am planning to use sp_send_db_mail inside of a SQL Server Job. Is this the best way of accomplishing this task?

Ideally, I would only like one email.

Update

After battling for ages!!! The solution I have found so far is to put it into a stored procedure and then execute the stored procedure like so:

EXEC msdb.dbo.sp_send_dbmail
        @recipients = N'<email>',
        @body = @body, 
        @subject = @subjectt,
        @profile_name = @ProfileName,
        @query ='exec test..sp_checks'

Is this ok for multiple select statements?

Best Answer

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.

Related Question