Today's script is a collection of simple queries I have saved for a time when I need to troubleshoot database mail problems.
/*
-------------------------------------
Summary: Database Mail Troubleshooting
SQL Server Versions: 2005 onwards
Written by: Chris McGowan
-------------------------------------
For more SQL resources, check out SQLServer365.blogspot.com
-------------------------------------
You may alter this code for your own purposes.
You may republish altered code as long as you give due credit.
You must obtain prior permission before blogging this code.
THIS CODE AND INFORMATION ARE PROVIDED "AS IS"
-------------------------------------
*/
-- Set database context
USE msdb
GO
-- Declare and set @dteDate variable
DECLARE @dteDate DATETIME
SET @dteDate = '20100714'
-- Check the event log records
SELECT *
FROM msdb.dbo.sysmail_event_log
WHERE log_date > @dteDate;
-- Check if mail is being sent
SELECT *
FROM msdb.dbo.sysmail_allitems
WHERE send_request_date > @dteDate
-- Check the mail queue state
EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'Mail' ;
GO
-- Check if the service broker is enabled
SELECT is_broker_enabled
FROM sys.databases
WHERE name = 'msdb';
GO
-- Start database mail
EXECUTE msdb.dbo.sysmail_start_sp;
GO
-- Check the members of the DatabaseMailUserRole role
EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole';
GO
-- Check associations between Database Mail profiles and database principals
EXEC msdb.dbo.sysmail_help_principalprofile_sp;
GO
-- Check which accounts are sending mail
SELECT sent_account_id, sent_date
FROM msdb.dbo.sysmail_sentitems;
GO
-- Enable database mail
EXEC sp_configure 'Database Mail XPs', 1
GO
-- Caution causes the buffer cache to be flushed!!!
RECONFIGURE WITH OVERRIDE
GO
Enjoy!
Chris
