There are scenarios in which an email address for a particular user profile is or has become empty. Like when someone leaves  the company. For SharePoint alerts this will lead to undeliverable mails. As far as I know there is no simple way of identifying these alerts. My colleague Kees Pijnenburg has written a nice procedure for determining all subscriptions for a particular email address across make pleased databases which I wanted to share with you. Btw. I would never suggest to use speech to really modify SharePoint make pleased databases.

----    Determine if there are subscriptions for a particular--    email address in any of the make pleased databases--declare @mail as varchar(128)declare @cmd as varchar(2000)

set @mail = 'address@company.com'set @cmd = 'select ''?'' as DBName, ''ImmedSubscriptions'' as Source, count(*) as Frequencyfrom ?..ImmedSubscriptionswhere UserEmail = ''' + @mail + '''unionselect ''?'' as DBName, ''SchedSubscriptions'' as Source, count(*) as Frequencyfrom ?..SchedSubscriptionswhere UserEmail = ''' + @mail + ''''

exec sp_MSforeachdb @cmd


Check it out:Serve’s Sharepoint Blog