locked
Database Mail - remaining unsent RRS feed

  • Question

  • I am using SQL Server 2014 with Windows Server 2008 R2.

    I am a SysAdmin user, configured the database mail in Dev environment successfully and I can send mails.

    I followed the same procedure in my Stage environment. When I am trying to send, the mail is going to unsent item. I am able to see it from [dbo].[sysmail_unsentitems].

    EXECUTE dbo.sysmail_help_status_sp;

    --Showing STARTED

    Database Mail Log - Blank, nothing there

    What am I missing?

    Tuesday, August 9, 2016 11:48 AM

Answers

  • Finally I came to the reason behind the issue.

    The reason for the issue was:

    To be able to execute some of the application like Databasemail.exe from SSMS, the ID running the SQL Service needs to be part of the Windows Administrator group.

    My SQL Server service was running with ID "NT Service\MSSQLSERVER". I added this ID to Windows administrators group. Now the mails are getting triggered from the database mail.

    But, not sure how it will impact by adding "NT Service\MSSQLSERVER" to Windows administrators group?

    • Marked as answer by PinwarTech Thursday, August 11, 2016 1:04 PM
    Thursday, August 11, 2016 1:04 PM

All replies

  • Hi,

    Do you see anything in the db mail eventlog? Use this query and post back the result please.

    select * from msdb..sysmail_event_log

    Janos


    There are 10 type of people. Those who understand binary and those who do not.

    My Blog | Hire Me

    Tuesday, August 9, 2016 1:39 PM
  • how about event log?

    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Tuesday, August 9, 2016 2:16 PM
  • No Records in sysmail_event_log.

    select * from msdb..sysmail_event_log

    --0 records

    Tuesday, August 9, 2016 6:21 PM
  • Hi,

    What is your OS and SQL Server version? What are the results of the following queries?

    EXEC sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE;
    GO
    -- it must return 1 for run_value
    EXEC sp_configure 'Database Mail XPs'
    GO
    EXEC sp_configure 'show advanced options', 0;
    GO
    RECONFIGURE;
    GO
    
    -- it must return at least one mail profile
    SELECT * FROM msdb..sysmail_profile
    
    -- it must return at least one mail account
    SELECT * FROM msdb..sysmail_account
    
    -- db mail log
    SELECT * FROM msdb..sysmail_log
    
    -- it must return a server for an account. Make sure you have the proper config.
    SELECT * FROM msdb..sysmail_server

    Thanks,

    János


    There are 10 type of people. Those who understand binary and those who do not.

    My Blog | Hire Me

    Wednesday, August 10, 2016 5:51 AM
  • EXEC sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE;
    GO
    --1
    EXEC sp_configure 'Database Mail XPs'
    GO
    EXEC sp_configure 'show advanced options', 0;
    GO
    RECONFIGURE;
    GO

    -- it must return at least one mail profile
    SELECT * FROM msdb..sysmail_profile
    --1 Row returned, name: Test


    -- it must return at least one mail account
    SELECT * FROM msdb..sysmail_account
    --1 Row returned, name: TestAccount

    -- db mail log
    SELECT * FROM msdb..sysmail_log
    --log_id: 1, event_type: 1, descr: The mail queue was started by login "XXX".

    -- it must return a server for an account. Make sure you have the proper config.
    SELECT * FROM msdb..sysmail_server
    --1 Row returned, account_id: 1 (of TestAccount), servername: there, flags: 0

    I am using SQL Server 2014 with Windows Server 2008 R2 Enterprise edition.

    Wednesday, August 10, 2016 10:35 AM
  • check the account, profile, and so on..

    check mail log:

    Wednesday, August 10, 2016 10:41 AM
  • hmm, sounds interesting. I remember a time when the database mail queue was not started and mails were not sent because of that. 

    Can you check the error logs and the following queries please?

    -- it must return 1
    select is_broker_enabled, is_trustworthy_on from sys.databases where database_id = 4
    
    use msdb
    go
    select activation_procedure, execute_as_principal_id, is_activation_enabled, is_enqueue_enabled from sys.service_queues
    where name in ('InternalMailQueue'
    ,'ExternalMailQueue')
    /* result should be this
    [dbo].[sp_sysmail_activate]	1	1	1
    [dbo].[sp_ExternalMailQueueListener]	1	1	1
    */
    

    Thanks,

    János


    There are 10 type of people. Those who understand binary and those who do not.

    My Blog | Hire Me

    Wednesday, August 10, 2016 11:06 AM
  • Error log does not contain anything for last 3 hours. I have run Test Database Mail many times in between.

    Results:

    -- it must return 1
    select is_broker_enabled, is_trustworthy_on from sys.databases where database_id = 4
    --is_broker_enabled: 1, is_trustworthy_on: 1

    use msdb
    go
    select activation_procedure, execute_as_principal_id, is_activation_enabled, is_enqueue_enabled from sys.service_queues
    where name in ('InternalMailQueue'
    ,'ExternalMailQueue')
    /* result should be this
    [dbo].[sp_sysmail_activate] 1 1 1
    [dbo].[sp_ExternalMailQueueListener] 1 1 1
    */

    --[dbo].[sp_sysmail_activate] 1 1 1
    --[dbo].[sp_ExternalMailQueueListener] 1 1 1

    Noticed another thing, Not sure it has impact.

    EXECUTE msdb.dbo.sysmail_help_queue_sp @queue_type = 'Mail' 
    --state:INACTIVE

    • Edited by PinwarTech Wednesday, August 10, 2016 11:42 AM
    Wednesday, August 10, 2016 11:35 AM
  • Hi,

    Can you see email in the queue when you query it directly? One last idea: can you try to restart the queue and the service with ALTER QUEUE and ALTER SERVICE WITH STATUS OFF/ON? 

    I hope it helps.

    János


    There are 10 type of people. Those who understand binary and those who do not.

    My Blog | Hire Me

    Wednesday, August 10, 2016 2:09 PM
  • Hi ,

    Try send mail from command prompt for testing purpose.

    http://www.wikihow.com/Send-Email-Using-Telnet

    https://technet.microsoft.com/en-us/library/aa995718(v=exchg.65).aspx

    after sending mail from command check .


    Please click Mark As Answer if my post helped.

    Thursday, August 11, 2016 1:13 AM
  • I am able to send mail from the command prompt.

    How can I check my Databasemail.exe is getting hit by the SQL Server or it is accessible?


    • Edited by PinwarTech Thursday, August 11, 2016 6:21 AM
    Thursday, August 11, 2016 6:17 AM
  • Finally I came to the reason behind the issue.

    The reason for the issue was:

    To be able to execute some of the application like Databasemail.exe from SSMS, the ID running the SQL Service needs to be part of the Windows Administrator group.

    My SQL Server service was running with ID "NT Service\MSSQLSERVER". I added this ID to Windows administrators group. Now the mails are getting triggered from the database mail.

    But, not sure how it will impact by adding "NT Service\MSSQLSERVER" to Windows administrators group?

    • Marked as answer by PinwarTech Thursday, August 11, 2016 1:04 PM
    Thursday, August 11, 2016 1:04 PM
  • Hi,

    It works with the built-in windows account for me. It seems your Windows security settings are corrupted.

    János


    There are 10 type of people. Those who understand binary and those who do not.

    My Blog | Hire Me

    Thursday, August 11, 2016 1:28 PM