A Script A Day - Day 2 - Database Mail Troubleshooting

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.co.uk

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

      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