All mail showing as "unsent" in sysmail_allitems (Database Mail)

• neellotus07

  Ten Centuries

  Points: 1245

  More actions

  February 9, 2017 at 1:18 am

  #334896

  Hi all,
  I configured "Database Mail" at my SQL - 2005 to send emails through Sql Management Studio as per below image :-
  
  But when i am trying to send test mail through "Database Mail" mail is not receiving at corresponding email address  so i troubleshoot and found unsent status in sysmail_allitems for all mail and i checked my mail server is okay.
  And i also tried login through SA and local domain user but mail status is unsent. I also assigned "DatabaseMailUserRolerole" role to local domain user but status is same. And i also run below queries to check where i am doing wrong but not getting solution so pls help me to solve same issue:-

  select * from msdb.dbo.sysmail_sentitems
  select * from msdb.dbo.sysmail_faileditems
  result is blank of above two queries
  select * from msdb.dbo.sysmail_event_log
  result of above query :-
  

  select * from msdb.dbo.sysmail_unsentitems
  select * from msdb.dbo.sysmail_allitems
  above sql query showing unsent status of all emails.

  please help me to solve the issue thanxxx..

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  February 9, 2017 at 12:12 pm

  #1927622

  There were some issues with Database Mail and SQL Server 2005 service packs not updating the mail related procedures. What service pack are you on? Some people have had to reapply service packs 2 or 3 to get mail working correctly. They were able to find the issues in the logs from installing the service packs. 
  Did you make sure database mail and broker are enabled? Both of these should return 1:
  SELECT value_in_use
  FROM sys.configurations
  WHERE name = 'Database Mail XPs';

  SELECT is_broker_enabled
FROM sys.databases
WHERE name='MSDB';

  Are you sure the SMTP configurations are correct? Did you verify the address with anyone? Are you sure SSL is not necessary? Does the account have access to the SMTP server and are relays allowed for that account?
  You can also try to ping the SMTP server from the SQL Server where you have the issues. You can also try to telnet to port 25 to the SMTP server from the SQL server.

  Sue

• Lowell

  SSC Guru

  Points: 323504

  More actions

  February 9, 2017 at 12:17 pm

  #1927625

  take a look at the last errors, you might be getting a very specific error, like relaying denied,  failed logins, etc.

  by default, database mail will retry once (within five minutes) and the mails that are still failed would have to be requeued.

  what errors are you seeing in this?
  SELECT top 100
mail.send_request_date As SentDate,
sent_status As Reason,
err.[description],
mail.*
FROM [msdb].[dbo].[sysmail_allitems] mail
inner join [msdb].[dbo].[sysmail_event_log] err
  ON err.mailitem_id = mail.mailitem_id
WHERE mail.sent_status <> 'sent'
  order by mailitem_id desc

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• neellotus07

  Ten Centuries

  Points: 1245

  More actions

  February 9, 2017 at 8:19 pm

  #1927677

  Sue_H - Thursday, February 9, 2017 12:12 PM

  There were some issues with Database Mail and SQL Server 2005 service packs not updating the mail related procedures. What service pack are you on? Some people have had to reapply service packs 2 or 3 to get mail working correctly. They were able to find the issues in the logs from installing the service packs. 
  Did you make sure database mail and broker are enabled? Both of these should return 1:
  SELECT value_in_use
  FROM sys.configurations
  WHERE name = 'Database Mail XPs';

  SELECT is_broker_enabled
FROM sys.databases
WHERE name='MSDB';

  Are you sure the SMTP configurations are correct? Did you verify the address with anyone? Are you sure SSL is not necessary? Does the account have access to the SMTP server and are relays allowed for that account?
  You can also try to ping the SMTP server from the SQL Server where you have the issues. You can also try to telnet to port 25 to the SMTP server from the SQL server.

  Sue

  Thanx Sue for your reply
  Database mail and broker are enabled as below image :-
  

  SMTP configurations are correct and i am sure SSL not required, replay coming when i ping SMTP server from SQL Server and email account is also okay.

• neellotus07

  Ten Centuries

  Points: 1245

  More actions

  February 9, 2017 at 8:23 pm

  #1927678

  Lowell - Thursday, February 9, 2017 12:17 PM

  take a look at the last errors, you might be getting a very specific error, like relaying denied,  failed logins, etc.

  by default, database mail will retry once (within five minutes) and the mails that are still failed would have to be requeued.

  what errors are you seeing in this?
  SELECT top 100
mail.send_request_date As SentDate,
sent_status As Reason,
err.[description],
mail.*
FROM [msdb].[dbo].[sysmail_allitems] mail
inner join [msdb].[dbo].[sysmail_event_log] err
  ON err.mailitem_id = mail.mailitem_id
WHERE mail.sent_status <> 'sent'
  order by mailitem_id desc

  Lowell thanx for ur reply
  and result of your query is blank as per below image :-
  

• neellotus07

  Ten Centuries

  Points: 1245

  More actions

  February 9, 2017 at 8:30 pm

  #1927679

  neellotus07 - Thursday, February 9, 2017 8:19 PM

  Sue_H - Thursday, February 9, 2017 12:12 PM

  There were some issues with Database Mail and SQL Server 2005 service packs not updating the mail related procedures. What service pack are you on? Some people have had to reapply service packs 2 or 3 to get mail working correctly. They were able to find the issues in the logs from installing the service packs. 
  Did you make sure database mail and broker are enabled? Both of these should return 1:
  SELECT value_in_use
  FROM sys.configurations
  WHERE name = 'Database Mail XPs';

  SELECT is_broker_enabled
FROM sys.databases
WHERE name='MSDB';

  Are you sure the SMTP configurations are correct? Did you verify the address with anyone? Are you sure SSL is not necessary? Does the account have access to the SMTP server and are relays allowed for that account?
  You can also try to ping the SMTP server from the SQL Server where you have the issues. You can also try to telnet to port 25 to the SMTP server from the SQL server.

  Sue

  Thanx Sue for your reply
  Database mail and broker are enabled as below image :-
  

  SMTP configurations are correct and i am sure SSL not required, replay coming when i ping SMTP server from SQL Server and email account is also okay.

  MS SQL SERVER Standard Edition with SP2

• Lowell

  SSC Guru

  Points: 323504

  More actions

  February 9, 2017 at 8:32 pm

  #1927680

  Ok ive seen that kind of situation before. You can try the sp_mail_stop and sp_mail_start to fix the service broker, but in the past ive had to completely stop both the sql agent and the sql service, and restart it, to get it to work again. Try that if you have a maint window, and ill look for my queries that check service brokers and stuck messages and report back

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• neellotus07

  Ten Centuries

  Points: 1245

  More actions

  February 9, 2017 at 9:05 pm

  #1927682

  neellotus07 - Thursday, February 9, 2017 8:19 PM

  Sue_H - Thursday, February 9, 2017 12:12 PM

  There were some issues with Database Mail and SQL Server 2005 service packs not updating the mail related procedures. What service pack are you on? Some people have had to reapply service packs 2 or 3 to get mail working correctly. They were able to find the issues in the logs from installing the service packs. 
  Did you make sure database mail and broker are enabled? Both of these should return 1:
  SELECT value_in_use
  FROM sys.configurations
  WHERE name = 'Database Mail XPs';

  SELECT is_broker_enabled
FROM sys.databases
WHERE name='MSDB';

  Are you sure the SMTP configurations are correct? Did you verify the address with anyone? Are you sure SSL is not necessary? Does the account have access to the SMTP server and are relays allowed for that account?
  You can also try to ping the SMTP server from the SQL Server where you have the issues. You can also try to telnet to port 25 to the SMTP server from the SQL server.

  Sue

  Thanx Sue for your reply
  Database mail and broker are enabled as below image :-
  

  SMTP configurations are correct and i am sure SSL not required, replay coming when i ping SMTP server from SQL Server and email account is also okay.

  MS SQL SERVER Standard Edition with SP2

  Lowell - Thursday, February 9, 2017 8:32 PM

  Ok ive seen that kind of situation before. You can try the sp_mail_stop and sp_mail_start to fix the service broker, but in the past ive had to completely stop both the sql agent and the sql service, and restart it, to get it to work again. Try that if you have a maint window, and ill look for my queries that check service brokers and stuck messages and report back

  Firstly i stop and start my sql server-2005 with agent then i sent mail that displaying unsent in "msdb.dbo.sysmail_allitems" then i run  sysmail_stop_sp and sysmail_start_sp but mail not receiving and it is still showing unsent in sysmail_allitems....pls help

• Lowell

  SSC Guru

  Points: 323504

  More actions

  February 10, 2017 at 6:19 am

  #1927741

  ok i found my diagnostics scripts.
  run these scripts in the msdb database.

  --confirm is_broker_enabled = 1
  SELECT DB_NAME([database_id]) AS DatabaseName,
   [database_id] ,
is_broker_enabled ,
CASE WHEN [is_broker_enabled] = 0 THEN 'ALTER DATABASE ' + QUOTENAME(name) + ' SET ENABLE_BROKER;' ELSE '' END
FROM sys.databases WHERE name= DB_NAME()

  if that is good,

  see if anything at all is int he transmission queue: empty=good, rows=bad:
  
select * from sys.transmission_queue ;

  next,  lets make sure the queue is on: if it is off, generate the command to fix it:
  SELECT db_name(database_id) AS DatabaseName,[database_id] ,
   [queue_id] ,
   [state] ,
   [last_empty_rowset_time] ,
   [last_activated_time] ,
   [tasks_waiting] FROM sys.dm_broker_queue_monitors
SELECT --[queues].[name] ,
   CASE WHEN [mon].is_receive_enabled = 1 AND [mon].[is_enqueue_enabled] = 1 THEN ''
     ELSE 'ALTER QUEUE ' + QUOTENAME(OBJECT_SCHEMA_NAME([queues].[object_id])) + '.' + QUOTENAME([queues].[name]) + ' WITH STATUS = ON'
   END AS ClearQueueCommand,
   [parti].[rows] AS CurrentRows,
   [mon].[name] ,
   [mon].[type] ,
   [mon].[type_desc] ,
   [mon].[create_date] ,
   [mon].[modify_date] ,
   [mon].[max_readers] ,
   [mon].[activation_procedure] ,
   [mon].[execute_as_principal_id] ,
   USER_NAME([mon].[execute_as_principal_id]) AS ExecutePrincipalName,
   [mon].[is_activation_enabled] ,
   [mon].[is_receive_enabled] ,
   [mon].[is_enqueue_enabled] ,
   [mon].[is_retention_enabled] ,
   [mon].[is_poison_message_handling_enabled],

     QUOTENAME(OBJECT_SCHEMA_NAME([queues].[object_id])) + '.' + QUOTENAME([queues].[name]) AS QualifiedName ,
   SCHEMA_NAME([queues].[schema_id]) AS SchemaName ,
   [queues].[name] AS ServiceBrokerName ,
   ' SELECT ''' + [queues].[name] + ''' AS ServiceBrokerQueueName, * FROM '
   + QUOTENAME(OBJECT_SCHEMA_NAME([queues].[object_id])) + '.' + QUOTENAME([queues].[name]) + ';' AS cmd ,
   ClearQueueCommand = 'WHILE EXISTS( SELECT ''' + [queues].[name] + ''' AS ServiceBrokerQueueName, * FROM ' + QUOTENAME(OBJECT_SCHEMA_NAME([queues].[object_id])) + '.' + QUOTENAME([queues].[name]) + ')
  BEGIN
   waitfor (
    receive top(1)
    conversation_group_id
    from ' + QUOTENAME([queues].[name]) + '
   ), timeout 1000;
  END'
FROM  [sys].[objects] AS objz
   INNER JOIN [sys].[partitions] AS parti ON [parti].[object_id] = objz.[object_id]
   INNER JOIN [sys].[objects] AS queues ON objz.[parent_object_id] = [queues].[object_id]
   INNER JOIN sys.[service_queues] [mon] ON [queues].[object_id] = [mon].[object_id]
WHERE [queues].[type_desc] = 'SERVICE_QUEUE'
   AND [parti].[index_id] = 1

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  February 10, 2017 at 8:21 am

  #1927797

  neellotus07 - Thursday, February 9, 2017 8:30 PM

  neellotus07 - Thursday, February 9, 2017 8:19 PM

  Thanx Sue for your reply
  Database mail and broker are enabled as below image :-
  

  SMTP configurations are correct and i am sure SSL not required, replay coming when i ping SMTP server from SQL Server and email account is also okay.

  MS SQL SERVER Standard Edition with SP2

  I did find the information on the service packs and database mail issues - you may or may not have the issue but I'd probably check to be sure. They were with SP2 and SP3 for SQL Server 2005. There were several different errors as well as mail not sending with no error messages, just sat unsent. It looks like all of them were from the service packs not updating the binaries but other mail objects were updated. Database Mail received a new version of the binary files and schema definitions with SP2 CU 13.
  So check your version with @@version and see if you are 9.00.3325 or higher.
  If your fine there, I'd check the SMTP logs since the unsent status usually means the SMTP server has not yet processed the e-mail message for whatever reason. So you may be able to figure out more from those logs.

  As a side note, you really need to upgrade that server. At a minimum you really want to be on service pack 4 but should really be on a higher version of SQL Server since support has ended for SQL Server 2005. 

  Sue

• neellotus07

  Ten Centuries

  Points: 1245

  More actions

  February 10, 2017 at 8:44 am

  #1927804

  Lowell - Friday, February 10, 2017 6:19 AM

  ok i found my diagnostics scripts.
  run these scripts in the msdb database.

  --confirm is_broker_enabled = 1
  SELECT DB_NAME([database_id]) AS DatabaseName,
   [database_id] ,
is_broker_enabled ,
CASE WHEN [is_broker_enabled] = 0 THEN 'ALTER DATABASE ' + QUOTENAME(name) + ' SET ENABLE_BROKER;' ELSE '' END
FROM sys.databases WHERE name= DB_NAME()

  if that is good,

  see if anything at all is int he transmission queue: empty=good, rows=bad:
  
select * from sys.transmission_queue ;

  next,  lets make sure the queue is on: if it is off, generate the command to fix it:
  SELECT db_name(database_id) AS DatabaseName,[database_id] ,
   [queue_id] ,
   [state] ,
   [last_empty_rowset_time] ,
   [last_activated_time] ,
   [tasks_waiting] FROM sys.dm_broker_queue_monitors
SELECT --[queues].[name] ,
   CASE WHEN [mon].is_receive_enabled = 1 AND [mon].[is_enqueue_enabled] = 1 THEN ''
     ELSE 'ALTER QUEUE ' + QUOTENAME(OBJECT_SCHEMA_NAME([queues].[object_id])) + '.' + QUOTENAME([queues].[name]) + ' WITH STATUS = ON'
   END AS ClearQueueCommand,
   [parti].[rows] AS CurrentRows,
   [mon].[name] ,
   [mon].[type] ,
   [mon].[type_desc] ,
   [mon].[create_date] ,
   [mon].[modify_date] ,
   [mon].[max_readers] ,
   [mon].[activation_procedure] ,
   [mon].[execute_as_principal_id] ,
   USER_NAME([mon].[execute_as_principal_id]) AS ExecutePrincipalName,
   [mon].[is_activation_enabled] ,
   [mon].[is_receive_enabled] ,
   [mon].[is_enqueue_enabled] ,
   [mon].[is_retention_enabled] ,
   [mon].[is_poison_message_handling_enabled],

     QUOTENAME(OBJECT_SCHEMA_NAME([queues].[object_id])) + '.' + QUOTENAME([queues].[name]) AS QualifiedName ,
   SCHEMA_NAME([queues].[schema_id]) AS SchemaName ,
   [queues].[name] AS ServiceBrokerName ,
   ' SELECT ''' + [queues].[name] + ''' AS ServiceBrokerQueueName, * FROM '
   + QUOTENAME(OBJECT_SCHEMA_NAME([queues].[object_id])) + '.' + QUOTENAME([queues].[name]) + ';' AS cmd ,
   ClearQueueCommand = 'WHILE EXISTS( SELECT ''' + [queues].[name] + ''' AS ServiceBrokerQueueName, * FROM ' + QUOTENAME(OBJECT_SCHEMA_NAME([queues].[object_id])) + '.' + QUOTENAME([queues].[name]) + ')
  BEGIN
   waitfor (
    receive top(1)
    conversation_group_id
    from ' + QUOTENAME([queues].[name]) + '
   ), timeout 1000;
  END'
FROM  [sys].[objects] AS objz
   INNER JOIN [sys].[partitions] AS parti ON [parti].[object_id] = objz.[object_id]
   INNER JOIN [sys].[objects] AS queues ON objz.[parent_object_id] = [queues].[object_id]
   INNER JOIN sys.[service_queues] [mon] ON [queues].[object_id] = [mon].[object_id]
WHERE [queues].[type_desc] = 'SERVICE_QUEUE'
   AND [parti].[index_id] = 1

  Lowell thanx for helping me and i run above 3 queries in msdb and found below results :-
  

• Lowell

  SSC Guru

  Points: 323504

  More actions

  February 10, 2017 at 8:52 am

  #1927808

  didn't realize you were running 2005, i should have read deeper. I don't have that version to test against.
  comment out that column name, adjust the query a bit till it works.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• neellotus07

  Ten Centuries

  Points: 1245

  More actions

  February 10, 2017 at 9:03 am

  #1927817

  Lowell - Friday, February 10, 2017 8:52 AM

  didn't realize you were running 2005, i should have read deeper. I don't have that version to test against.
  comment out that column name, adjust the query a bit till it works.

  I executed query and attached result in excel file.

  

• Lowell

  SSC Guru

  Points: 323504

  More actions

  February 10, 2017 at 9:09 am

  #1927818

  notice the item with 40 rows?
  that's the one that's stuck.

  run teh "ClearQueueCommand" that was in the results:
  WHILE EXISTS(SELECT
     'ExternalMailQueue' AS ServiceBrokerQueueName,
     *
     FROM
     [dbo].[ExternalMailQueue])
BEGIN
  WAITFOR ( receive TOP(1) conversation_group_id FROM [ExternalMailQueue] ), timeout 1000;
END


  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• neellotus07

  Ten Centuries

  Points: 1245

  More actions

  February 10, 2017 at 9:23 am

  #1927823

  Lowell - Friday, February 10, 2017 9:09 AM

  notice the item with 40 rows?
  that's the one that's stuck.

  run teh "ClearQueueCommand" that was in the results:
  WHILE EXISTS(SELECT
     'ExternalMailQueue' AS ServiceBrokerQueueName,
     *
     FROM
     [dbo].[ExternalMailQueue])
BEGIN
  WAITFOR ( receive TOP(1) conversation_group_id FROM [ExternalMailQueue] ), timeout 1000;
END


  Pls tell me how to execute "ClearQueueCommand" to clear the queue