Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Corrupt Page Notification

By Henrico Bekker, 2017/03/03

Create the trigger after modifying your mail profile settings, and any additional attachment types if you wish.

Simple technique to ensure you are informed if any content is written.

CREATE TRIGGER usr_suspect_page
ON msdb.[dbo].[suspect_pages]
FOR INSERT
AS
Begin
   
exec msdb.dbo.sp_send_dbmail
@profile_name = 'yourmailprofilename',
@recipients = 'dba@mail.com',
@subject = 'Suspect page row written - check details',
@body_format='HTML',
@query_result_header=1,
@execute_query_database='model',
@query = 
'select * from msdb.[dbo].[suspect_pages]',
@query_result_separator=' ',
@attach_query_result_as_file = 0, --set to 1 for true, this script will keep results in email body
@query_attachment_filename = 'msdb_suspect_rows.csv' -- will only attach when prior parameter set TRUE
end

Total article views: 429 | Views in the last 30 days: 5