SQL Server / Missing Indexes Query / DMV / Email Alert

This is an example of missing index query (DMV) of the database within email alert, we already had examples how you can use sp_send_dbmail stored procedure from msdb database to send backup size email daily,  long-running queries report and how to send missing indexes (Cached Plans) report via email. This is just another use case of sp_send_dbmail stored procedure.

I used following arguments to achieve this

@profile_name (name of an existing Database Mail profile, type sysname, default NULL),

If you are not sure about your profile account use the following script, it shows all the accounts for all profiles in the instance.

@recipients (Is a semicolon-delimited list of e-mail addresses to send the message to, varchar(max)),

@body (Is the body of the e-mail message, nvarchar(max)),

@subject (Is the subject of the e-mail message, nvarchar(255)),

@body_format (Is the format of the message body, varchar(20))

More information about this system stored procedure you can find here sp_send_dbmail (Transact-SQL)

 

 

Your email result should be similar to

 

Script Download:

The script with usage example is available for download from https://gallery.technet.microsoft.com/scriptcenter/SQL-Server-Database-0aef26fd

Summary:

Use sp_send_dbmail stored procedure from msdb database to send missing index report (DMVs) to email weekly/monthly.

Note.
Please be aware and use this script just as a guidance. I do not advise you to create all the missing indexes suggested by this script.

 

Similar Posts:

2 Responses to “ “SQL Server / Missing Indexes Query / DMV / Email Alert”

  1. candy says:

    It shows incorrect syntax can you please check once

  2. candy says:

    It shows incorrect syntax can you please check once
    Msg 102, Level 15, State 1, Line 13
    Incorrect syntax near ‘<'.
    Msg 102, Level 15, State 1, Line 40
    Incorrect syntax near '<'.
    Msg 102, Level 15, State 1, Line 57
    Incorrect syntax near ','),'.
    Msg 105, Level 15, State 1, Line 101
    Unclosed quotation mark after the character string ''.

Leave a Reply

Your email address will not be published. Required fields are marked *