Strange Working of Sql server database mail- Need urgent help

  • 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.

  • 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

  • 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.

  • Guys please post your comments.

    your help is highly awaited.

  • Is there anyone who can help me out.? 😎

  • anything in the logs?

  • 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

  • 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.

  • 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.

  • Thanks for the suggestions. Guys.

    But problem is solved.

    Just Upgraded SP 1 to SP 2. :-D:-D

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply