Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Strange Working of Sql server database mail- Need urgent help Expand / Collapse
Author
Message
Posted Wednesday, September 11, 2013 11:48 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 12:53 AM
Points: 525, Visits: 648
It may be a stupid question/problem,,, But i am struck up in a situation where i am not able to send emails from database email.

After much surfing on internet to find some solution, i came cross a script where it checks for the DB mail service status, if it is stopped, then again a script to start the service.
It is as Follows:

-- See if database mail (DM) is started
EXEC msdb.dbo.sysmail_help_status_sp ------- This Gives Output of STOPPED
GO

-- Stop the service
EXEC msdb.dbo.sysmail_stop_sp ---- This runs successfully.
GO

-- See if database mail (DM) is stopped
EXEC msdb.dbo.sysmail_help_status_sp ----- This Gives STOPPED (as expected)
GO

-- Start the service
EXEC msdb.dbo.sysmail_start_sp ---- This runs successfully.
GO



-- See if database mail (DM) is started
EXEC msdb.dbo.sysmail_help_status_sp ---- Now this executed just after the previous Query and give the
output as STARTED, but after few seconds when i
again execute this. output goes back to STOPPED

GO


Why does it STOPPED automatically when once it is started.
Post #1493836
Posted Wednesday, September 11, 2013 12:19 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 1:45 PM
Points: 1,494, Visits: 1,774
I had this issue some time ago and resolved by following the steps in http://technet.microsoft.com/en-us/library/ms190606(v=sql.105).aspx
Post #1493842
Posted Wednesday, September 11, 2013 9:48 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 12:53 AM
Points: 525, Visits: 648
batgirl (9/11/2013)
I had this issue some time ago and resolved by following the steps in http://technet.microsoft.com/en-us/library/ms190606(v=sql.105).aspx


Thanks for the reply.
Will try the solution proposed by you.
but can u please explain me why this is happening.?
why database starts for few seconds and then it stops later.
Post #1493987
Posted Wednesday, September 11, 2013 9:50 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 12:53 AM
Points: 525, Visits: 648
Guys please post your comments.

your help is highly awaited.
Post #1493988
Posted Wednesday, September 11, 2013 11:56 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 12:53 AM
Points: 525, Visits: 648
Is there anyone who can help me out.?
Post #1494004
Posted Thursday, September 12, 2013 2:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 28, 2014 6:43 AM
Points: 153, Visits: 2,193
anything in the logs?
Post #1494042
Posted Thursday, September 12, 2013 3:37 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, November 28, 2014 8:09 AM
Points: 316, Visits: 592
The mail agent only runs when it has emails to process, you can see that here:

select * from msdb.dbo.sysmail_log 

You can then check emails that were sent here:

select * from msdb.dbo.sysmail_mailitems

There are other mail tables that can be queried for profiles etc.

You can also check that database mail is enabled with this:

sp_configure 'Database Mail XPs'



MCITP SQL 2005, MCSA SQL 2012
Post #1494073
Posted Thursday, September 12, 2013 6:05 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 1:45 PM
Points: 1,494, Visits: 1,774
Mr. Kapsicum (9/11/2013)
batgirl (9/11/2013)
I had this issue some time ago and resolved by following the steps in http://technet.microsoft.com/en-us/library/ms190606(v=sql.105).aspx


Thanks for the reply.
Will try the solution proposed by you.
but can u please explain me why this is happening.?
why database starts for few seconds and then it stops later.


I can only guess that the configuration was not done properly until I followed the document. After changing the e-mail system, you must restart the SQL Server Agent service for the change to take effect.
Post #1494116
Posted Thursday, September 12, 2013 6:11 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 11:15 AM
Points: 763, Visits: 2,292
On a few of my instances, I've increased the Database Mail Executable Minimum Lifetime (seconds) from 500 to 43200. You can find that by risk-clicking on database mail --> Configure Database Mail --> View or Change system parameters.

I don't remember exactly where that number came from, but I was having a similar issue where when I wanted to send out an email via database mail, like when a job failed, it was always stopped.




The opinions expressed herein are strictly personal and do not necessarily reflect the views or policies of my employer.
Post #1494120
Posted Thursday, September 12, 2013 10:03 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 12:53 AM
Points: 525, Visits: 648
Thanks for the suggestions. Guys.


But problem is solved.

Just Upgraded SP 1 to SP 2.
Post #1494433
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse