Database Mail Troubleshooting

Database Mail Activation Status:

EXEC msdb.dbo.sysmail_help_status_sp ;

Database Mail activation is not started, execute the following statement to start it:

EXEC msdb.dbo.sysmail_start_sp

Check the status of the mail queue:

EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail' ;

Check which profiles have been set up:

SELECT * FROM msdb.dbo.sysmail_profile

Profiles which can successfully send mail:

SELECT sent_account_id, sent_date FROM msdb.dbo.sysmail_sentitems ;

View error log:

SELECT * FROM msdb.dbo.sysmail_event_log ;

Users who can send mail (Apart from inherited access via sysadmin or msdb.db_owner roles):

EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole' ;

Add users to role:

sp_addrolemember @rolename = 'DatabaseMailUserRole', @membername = '<database user>'

List users and their access to profiles:

EXEC msdb.dbo.sysmail_help_principalprofile_sp ;

Troubleshoot mail items:

select distinct *
from msdb.dbo.sysmail_log l
join msdb.dbo.sysmail_mailitems i on (l.mailitem_id = i.mailitem_id)
join msdb.dbo.sysmail_profile p on (i.profile_id = p.profile_id)
where l.mailitem_id = 20922

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: