Query to find mail profiles created in SQL Server

Query to find the Mail profiles created in the instance of SQL Server Mailing purpose

In general practise DBA would definetly create a mail profile in the SQL Server to the alert information and automated jobs details to his/support group email id.


Query to find the mail profiles:

Use MSDB
GO
select sa.account_id,spa.profile_id,sa.name,sa.email_address,sa.display_name, sp.name as profile_name from sysmail_profile sp join sysmail_profileaccount spa on sp.profile_id=spa.profile_id join sysmail_account sa on spa.account_id=sa.account_id
If you have just setup the mail profile/database profile and want to test and see it in the log, You have to check it out in "sysmail_event" of MSDB database.

If mail is failing please check the log to find out the issue.

Query to check the error related to failed mail items.

Use MSDB
Go

SELECT items.subject,items.last_mod_date
,l.description FROM dbo.sysmail_faileditems as itemsINNER JOIN dbo.sysmail_event_log AS l
ON items.mailitem_id = l.mailitem_idWHERE items.recipients LIKE '%DBATEAM%'
OR items.copy_recipients LIKE '%DBATEAM%'
OR items.blind_copy_recipients LIKE '%DBATEAM%'

Hope the above helps
Thanks