Check if Database Mail is running

It's great to have SQL Server Agent Alerts set up to notify the DBAs when something is broken, but if you have emails set to send it's not much use if Database Mail isn't running; and it's not crazy that sometimes it can just stop and not start up.

This should really be part of your automated daily checks. I'm not using policies yet but here's a quick one to indicate if it's stopped and start it again.

Set	Nocount On

If Object_Id('tempdb..#Status') Is Not Null
	Drop Table #Status
Go

Create Table #Status (
	[Status] Nvarchar(100)
)

Insert	#Status
Exec 	msdb.dbo.sysmail_help_status_sp

If Not Exists (
	Select	Top 1
			0
	From	#Status
	Where	Status = 'STARTED'
)
Begin
	Raiserror ('Database Mail was not running, attempting to restart', 16, 1) With Nowait
	Exec	msdb.dbo.sysmail_start_sp
End