DECLARE @days_ago_start INT

---------------------------------------------------------------------------------------

SET @days_ago_start = 7

---------------------------------------------------------------------------------------

SELECT  fi.send_request_date AS send_date,
        fi.send_request_user AS send_user,
        fi.recipients,
        fi.copy_recipients,
        fi.blind_copy_recipients,
        fi.[subject],
        fi.body,
        fi.sent_status,
        p.name AS profile_name,
        fi.body_format,
        fi.importance,
        fi.sensitivity,
        fi.file_attachments,
        fi.attachment_encoding,
        fi.query,
        fi.execute_query_database,
        fi.attach_query_result_as_file,
        fi.query_result_header,
        fi.query_result_width,
        fi.query_result_separator,
        fi.exclude_query_output,
        fi.append_query_error,
        fi.sent_account_id,
        fi.sent_date,
        fi.last_mod_date,
        fi.last_mod_user,
        fi.mailitem_id
FROM    msdb.dbo.sysmail_faileditems fi
        JOIN msdb.dbo.sysmail_profile p ON fi.profile_id = p.profile_id
WHERE   fi.send_request_date > DATEADD(day, -@days_ago_start, GETDATE())
ORDER BY fi.send_request_date ;

--SELECT * FROM msdb.dbo.sysmail_log WHERE mailitem_id IN (9265, 9303)

-- failed message log
SELECT  l.[description] AS log_description,
        mi.recipients,
        mi.copy_recipients,
        mi.blind_copy_recipients,
        mi.[subject],
        mi.body,
        mi.body_format,
        mi.importance,
        mi.sensitivity,
        mi.append_query_error,
        mi.send_request_date,
        mi.send_request_user,
        mi.sent_account_id,
        CASE mi.sent_status
          WHEN 0 THEN 'unsent'
          WHEN 1 THEN 'sent'
          WHEN 3 THEN 'retrying'
          ELSE 'failed'
        END AS sent_status,
        mi.sent_date,
        mi.last_mod_date,
        mi.last_mod_user,
        N'EXEC msdb.dbo.sp_send_dbmail @profile_name=''' + p.name + N''',@recipients=''' + mi.recipients + N''',@subject=''' + mi.[subject]
        + N''',@body_format=''' + mi.body_format + N''',@body=''' + mi.body + N'''' AS resend_exec
FROM    msdb.dbo.sysmail_mailitems mi
        JOIN msdb.dbo.sysmail_log l ON mi.mailitem_id = l.mailitem_id
        JOIN msdb.dbo.sysmail_profile p ON mi.profile_id = p.profile_id
WHERE   mi.send_request_date > DATEADD(day, -@days_ago_start, GETDATE())
        AND mi.sent_status NOT IN (1, 3)
ORDER BY mi.send_request_date DESC ;

--SELECT TOP 10
--        *
--FROM    msdb.dbo.sysmail_log
--WHERE   log_date > DATEADD(day, -1, GETDATE())
--ORDER BY log_date DESC

USE msdb
GO

DECLARE @ProfileName VARCHAR(35)
DECLARE @AccountName VARCHAR(35)
SET  @ProfileName = 'Database_Mail_Profile'

SET @AccountName = 'CYP_DW_Mail_Account'

IF EXISTS(

SELECT * FROM msdb.dbo.sysmail_profileaccount pa

      JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id

      JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id

WHERE

      p.name = @ProfileName AND

      a.name = @AccountName)

BEGIN

      PRINT 'Deleting Profile Account'

      EXECUTE sysmail_delete_profileaccount_sp

      @profile_name = @ProfileName,

      @account_name = @AccountName

END

 

IF EXISTS(

SELECT * FROM msdb.dbo.sysmail_profile p

WHERE p.name = @ProfileName)

BEGIN

      PRINT 'Deleting Profile.'

      EXECUTE sysmail_delete_profile_sp

      @profile_name = @ProfileName

END

 

IF EXISTS(

SELECT * FROM msdb.dbo.sysmail_account a

WHERE a.name = @AccountName)

BEGIN

      PRINT 'Deleting Account.'

      EXECUTE sysmail_delete_account_sp

      @account_name = @AccountName

END

-- Enable Database Mail for this instance

EXECUTE sp_configure 'show advanced', 1;

RECONFIGURE;

EXECUTE sp_configure 'Database Mail XPs',1;

RECONFIGURE;

GO


-- Create a Database Mail account

EXECUTE msdb.dbo.sysmail_add_account_sp

    @account_name = 'Primary Account',

    @description = 'Account used by all mail profiles.',

    @email_address = 'myaddress@mydomain.com',

    @replyto_address = 'myaddress@mydomain.com',

    @display_name = 'Database Mail',

    @mailserver_name = 'mail.mydomain.com';

 

-- Create a Database Mail profile

EXECUTE msdb.dbo.sysmail_add_profile_sp

    @profile_name = 'Default Public Profile',

    @description = 'Default public profile for all users';

 

-- Add the account to the profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

    @profile_name = 'Default Public Profile',

    @account_name = 'Primary Account',

    @sequence_number = 1;

 

-- Grant access to the profile to all msdb database users

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

    @profile_name = 'Default Public Profile',

    @principal_name = 'public',

    @is_default = 1;

GO

 

--send a test email

EXECUTE msdb.dbo.sp_send_dbmail

    @subject = 'Test Database Mail Message',

    @recipients = 'testaddress@mydomain.com',

    @query = 'SELECT @@SERVERNAME';

GO

SELECT items.subject,
    items.last_mod_date
    ,l.description FROM dbo.sysmail_faileditems as items
INNER JOIN dbo.sysmail_event_log AS l
    ON items.mailitem_id = l.mailitem_id

SELECT *
FROM sysmail_mailitems
GO

GO
SELECT *
FROM sysmail_log
GO

SELECT *
FROM sysmail_sentitems

SELECT *
FROM sysmail_allitems

SELECT * 
FROM sysmail_event_log