Database Mail stops working on SQL 2005

We've encountered a problem on several SQL 2005 installations where
database mail completely stops working for no reason. Has anyone else
encountered this? This only affects calls to sp_send_dbmail and job
alerts, etc. When I right-click DBMail in SSMS and choose Send Test
Email - that one works. But none of the stuff that actually WANT to
work. Any help would be appreciated. No errors anywhere - just the
message never arrives.Did sp_send_dbmail ever work? Did you enable service broker in the msdb database?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"CoreyB" <unc27932@.yahoo.com> wrote in message
news:1142005500.961901.109950@.j33g2000cwa.googlegroups.com...
> We've encountered a problem on several SQL 2005 installations where
> database mail completely stops working for no reason. Has anyone else
> encountered this? This only affects calls to sp_send_dbmail and job
> alerts, etc. When I right-click DBMail in SSMS and choose Send Test
> Email - that one works. But none of the stuff that actually WANT to
> work. Any help would be appreciated. No errors anywhere - just the
> message never arrives.
>|||Yes - we were using sp_send_dbmail in our SPs and they were running
great. Then they just stopped working - or should I say stopped
delivering. The status in the sys tables say that the messages were
sent. I've done the all the checks found on MS troubleshooting
page...
http://msdn2.microsoft.com/en-US/library/ms189959(SQL.90).aspx
This included checking to be sure service broker was enabled, which it
was, and dbmail was started. I'm fairly confident it's not the SMTP
server b/c the manual test in the GUI of SSMS works fine.|||Can you run these statements and post back the result?
select count(*) from msdb.dbo.InternalMailQueue
select count(*) from msdb.dbo.ExternalMailQueue
select count(*) from msdb.sys.transmission_queue
select distinct transmission_status from msdb.sys.transmission_queue
select sq.name,qm.* from sys.dm_broker_queue_monitors qm
join msdb.sys.service_queues sq on qm.queue_id = sq.object_id
where database_id = db_id('msdb')
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"CoreyB" <unc27932@.yahoo.com> wrote in message
news:1142005500.961901.109950@.j33g2000cwa.googlegroups.com...
> We've encountered a problem on several SQL 2005 installations where
> database mail completely stops working for no reason. Has anyone else
> encountered this? This only affects calls to sp_send_dbmail and job
> alerts, etc. When I right-click DBMail in SSMS and choose Send Test
> Email - that one works. But none of the stuff that actually WANT to
> work. Any help would be appreciated. No errors anywhere - just the
> message never arrives.
>|||Here are the results of those SQL statements...
select count(*) from msdb.dbo.InternalMailQueue
0
select count(*) from msdb.dbo.ExternalMailQueue
0
select count(*) from msdb.sys.transmission_queue
0
select distinct transmission_status from msdb.sys.transmission_queue
No Rows Returned
select sq.name,qm.* from sys.dm_broker_queue_monitors qm
join msdb.sys.service_queues sq on qm.queue_id = sq.object_id
where database_id = db_id('msdb')
InternalMailQueue 4 843150049 INACTIVE 2006-03-10
16:29:19.997 2006-03-10 16:29:19.983 0
ExternalMailQueue 4 875150163 INACTIVE 2006-03-10
16:39:19.957 2006-03-10 16:24:42.243 0
Remus Rusanu [MSFT] wrote:
> Can you run these statements and post back the result?
> select count(*) from msdb.dbo.InternalMailQueue
> select count(*) from msdb.dbo.ExternalMailQueue
> select count(*) from msdb.sys.transmission_queue
> select distinct transmission_status from msdb.sys.transmission_queue
> select sq.name,qm.* from sys.dm_broker_queue_monitors qm
> join msdb.sys.service_queues sq on qm.queue_id = sq.object_id
> where database_id = db_id('msdb')
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "CoreyB" <unc27932@.yahoo.com> wrote in message
> news:1142005500.961901.109950@.j33g2000cwa.googlegroups.com...
> > We've encountered a problem on several SQL 2005 installations where
> > database mail completely stops working for no reason. Has anyone else
> > encountered this? This only affects calls to sp_send_dbmail and job
> > alerts, etc. When I right-click DBMail in SSMS and choose Send Test
> > Email - that one works. But none of the stuff that actually WANT to
> > work. Any help would be appreciated. No errors anywhere - just the
> > message never arrives.
> >|||I'm still hoping someone might know about this...anyone'
CoreyB wrote:
> Here are the results of those SQL statements...
> select count(*) from msdb.dbo.InternalMailQueue
> 0
> select count(*) from msdb.dbo.ExternalMailQueue
> 0
> select count(*) from msdb.sys.transmission_queue
> 0
> select distinct transmission_status from msdb.sys.transmission_queue
> No Rows Returned
>
> select sq.name,qm.* from sys.dm_broker_queue_monitors qm
> join msdb.sys.service_queues sq on qm.queue_id = sq.object_id
> where database_id = db_id('msdb')
> InternalMailQueue 4 843150049 INACTIVE 2006-03-10
> 16:29:19.997 2006-03-10 16:29:19.983 0
> ExternalMailQueue 4 875150163 INACTIVE 2006-03-10
> 16:39:19.957 2006-03-10 16:24:42.243 0
>
> Remus Rusanu [MSFT] wrote:
> > Can you run these statements and post back the result?
> >
> > select count(*) from msdb.dbo.InternalMailQueue
> > select count(*) from msdb.dbo.ExternalMailQueue
> > select count(*) from msdb.sys.transmission_queue
> > select distinct transmission_status from msdb.sys.transmission_queue
> >
> > select sq.name,qm.* from sys.dm_broker_queue_monitors qm
> > join msdb.sys.service_queues sq on qm.queue_id = sq.object_id
> > where database_id = db_id('msdb')
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> > HTH,
> > ~ Remus Rusanu
> >
> > SQL Service Broker
> > http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
> >
> >
> > "CoreyB" <unc27932@.yahoo.com> wrote in message
> > news:1142005500.961901.109950@.j33g2000cwa.googlegroups.com...
> > > We've encountered a problem on several SQL 2005 installations where
> > > database mail completely stops working for no reason. Has anyone else
> > > encountered this? This only affects calls to sp_send_dbmail and job
> > > alerts, etc. When I right-click DBMail in SSMS and choose Send Test
> > > Email - that one works. But none of the stuff that actually WANT to
> > > work. Any help would be appreciated. No errors anywhere - just the
> > > message never arrives.
> > >|||CoreyB,
I had similar symptoms but not identical. Database Mail stopped
working unexpectedly including send test email as well as alerts.
Also, database mail log had no activity since a couple of days ago when
it stopped working. I had 37 messages unsent.
I double-checked the following:
1. database mail enabled via surface configuration
2. proper agent and profiles setup
3. service broker message delivery activated
4. database mail in mail host database started
All looked good so far...
5. messages stuck in queue -- yes, 37 total 6. status of messages in
queue -- status "unsent" which traces back to problem with database
mail configuration or external program not getting called for some
reason.
So I basically navigated to location of databasemail90.exe and
double-clicked to run manually and viola, messages started flowing.
However, CMD window stayed open longer than expected so I closed
abruptly.
After this, Step 4 above, "database mail started" now showed status of
"stopped". I ran a command in SQL studio to restart and now things
worked as expected -- mail flowing without me having to double-click
anything.
In summary, my observation is that the external program,
databasemail90.exe was in a "bad state" and required me to kick start
it manually.
http://msdn2.microsoft.com/en-US/library/ms189959(SQL.90).aspx
Here is relevant SQL obtained from MSDN troubleshooting guides if
anyone is interested:
--check to see if service broker is enabled SELECT is_broker_enabled
FROM sys.databases WHERE name = 'msdb' ;
--check to see if database mail is started in msdb database use msdb go
EXECUTE dbo.sysmail_help_status_sp ;
--start database mail if necessary
EXECUTE dbo.sysmail_start_sp ;
--check status of mail queue
sysmail_help_queue_sp @.queue_type = 'Mail' ;
--check database mail event log
select * from sysmail_event_log
--check database mail queue for status of all items select * from
sysmail_allitems
select * from sysmail_allitems