Sql-server – How to export the output of sp_AskBrent

database-mailformatsp-askbrentsql server

We have an instance that randomly spikes the CPU. I want to create an alert that fires on CPU over 90% and automatically calls a job that runs sp_AskBrent and emails me the output. However, the output is unreadable in either Text or HTML output. It doesn't even go well into an Excel spread sheet. How can I get the info in a readable format?

Best Answer

Make the alert run a job that runs the EXEC msdb.dbo.sp_send_dbmail with the @query parameter:

EXEC dbo.sp_send_dbmail @profile_name = 'mail_profile'
    , @recipients = 'some@mail.com'
    , @subject = 'some subject'
    , @body = 'the body text goes here'
    , @query = 'EXEC sp_AskBrent;'
    , @attach_query_result_as_file = 1;

The @attach_query_result_as_file = 1 parameter will send the results as an attachment in text format, which should certainly be readable. If you want, you can export the results in comma-delimited format using the @query_result_separator parameter.

From the MSDN page for sp_send_dbmail:

This example sends an e-mail message to Dan Wilson using the e-mail address danw@Adventure-Works.com. The message has the subject Work Order Count, and executes a query that shows the number of work orders with a DueDate less than two days after April 30, 2004. Database Mail attaches the result as a text file.

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Adventure Works Administrator',
    @recipients = 'danw@Adventure-Works.com',
    @query = 'SELECT COUNT(*) FROM AdventureWorks2012.Production.WorkOrder
                  WHERE DueDate > ''2004-04-30''
                  AND  DATEDIFF(dd, ''2004-04-30'', DueDate) < 2' ,
    @subject = 'Work Order Count',
    @attach_query_result_as_file = 1 ;

There are many options for that stored proc; check the MSDN page and likely you'll get the output exactly how you want it.

Related Question