Problem with Database mail

  • We have SQL SERVER 2008 R2,which Database mail does not work properly.When I try to send test mail,it says it has been queued but it does not being delivered.

    Here are some point about about Database mail configuration

    1- Database mail is enabled.

    3- database mail is started

    4- the status of mail queue is "InActive"

    5- The size of mail queue is 9025 and status is Inactive and the status queue is empty(length is 0) and it has been actived a long time ago.

    6- service Broker is enabled.

    7- When I ran the databasemail.exe manually nothing happened.

    8- In mail log shows only The mail queue was started or stopped and it does not show any failure or problem.

    Note : I have one Profile and one account in that profile.

    I wil be grateful,if someone help me on this.

    Thanks

    Aspet

  • Here is what I use to monitor Database Mail. Run it in your environment and see if it turns up any useful information.

    DECLARE @days_ago_start INT

    ---------------------------------------------------------------------------------------

    SET @days_ago_start = 7

    ---------------------------------------------------------------------------------------

    SELECT fi.send_request_date AS send_date,

    fi.send_request_user AS send_user,

    fi.recipients,

    fi.copy_recipients,

    fi.blind_copy_recipients,

    fi.[subject],

    fi.body,

    fi.sent_status,

    p.name AS profile_name,

    fi.body_format,

    fi.importance,

    fi.sensitivity,

    fi.file_attachments,

    fi.attachment_encoding,

    fi.query,

    fi.execute_query_database,

    fi.attach_query_result_as_file,

    fi.query_result_header,

    fi.query_result_width,

    fi.query_result_separator,

    fi.exclude_query_output,

    fi.append_query_error,

    fi.sent_account_id,

    fi.sent_date,

    fi.last_mod_date,

    fi.last_mod_user,

    fi.mailitem_id

    FROM msdb.dbo.sysmail_faileditems fi

    JOIN msdb.dbo.sysmail_profile p ON fi.profile_id = p.profile_id

    WHERE fi.send_request_date > DATEADD(day, -@days_ago_start, GETDATE())

    ORDER BY fi.send_request_date ;

    --SELECT * FROM msdb.dbo.sysmail_log WHERE mailitem_id IN (9265, 9303)

    -- failed message log

    SELECT l.[description] AS log_description,

    mi.recipients,

    mi.copy_recipients,

    mi.blind_copy_recipients,

    mi.[subject],

    mi.body,

    mi.body_format,

    mi.importance,

    mi.sensitivity,

    mi.append_query_error,

    mi.send_request_date,

    mi.send_request_user,

    mi.sent_account_id,

    CASE mi.sent_status

    WHEN 0 THEN 'unsent'

    WHEN 1 THEN 'sent'

    WHEN 3 THEN 'retrying'

    ELSE 'failed'

    END AS sent_status,

    mi.sent_date,

    mi.last_mod_date,

    mi.last_mod_user,

    N'EXEC msdb.dbo.sp_send_dbmail @profile_name=''' + p.name + N''',@recipients=''' + mi.recipients + N''',@subject=''' + mi.[subject]

    + N''',@body_format=''' + mi.body_format + N''',@body=''' + mi.body + N'''' AS resend_exec

    FROM msdb.dbo.sysmail_mailitems mi

    JOIN msdb.dbo.sysmail_log l ON mi.mailitem_id = l.mailitem_id

    JOIN msdb.dbo.sysmail_profile p ON mi.profile_id = p.profile_id

    WHERE mi.send_request_date > DATEADD(day, -@days_ago_start, GETDATE())

    AND mi.sent_status NOT IN (1, 3)

    ORDER BY mi.send_request_date DESC ;

    --SELECT TOP 10

    -- *

    --FROM msdb.dbo.sysmail_log

    --WHERE log_date > DATEADD(day, -1, GETDATE())

    --ORDER BY log_date DESC

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Also when I checked msdb.dbo.sysmail_event_log tables,I could not find the following events.

    DatabaseMail process is started

    Databasemail is shuttingdown

    It seems databasemail doesnot restart automatically ,when there is message in Message Queue.

    I checked the Servive Broker one more time and it was enabled.

  • I just configured Database Mail on 3 Servers on Friday. Make sure the login you use for the account does not expire.

    To clean up I would do the following:

    USE msdb

    GO

    DECLARE @ProfileName VARCHAR(35)

    DECLARE @AccountName VARCHAR(35)

    SET @ProfileName = 'Database_Mail_Profile'

    SET @AccountName = 'CYP_DW_Mail_Account'

    IF EXISTS(

    SELECT * FROM msdb.dbo.sysmail_profileaccount pa

    JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id

    JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id

    WHERE

    p.name = @ProfileName AND

    a.name = @AccountName)

    BEGIN

    PRINT 'Deleting Profile Account'

    EXECUTE sysmail_delete_profileaccount_sp

    @profile_name = @ProfileName,

    @account_name = @AccountName

    END

    IF EXISTS(

    SELECT * FROM msdb.dbo.sysmail_profile p

    WHERE p.name = @ProfileName)

    BEGIN

    PRINT 'Deleting Profile.'

    EXECUTE sysmail_delete_profile_sp

    @profile_name = @ProfileName

    END

    IF EXISTS(

    SELECT * FROM msdb.dbo.sysmail_account a

    WHERE a.name = @AccountName)

    BEGIN

    PRINT 'Deleting Account.'

    EXECUTE sysmail_delete_account_sp

    @account_name = @AccountName

    END

    I use something like I found in Dan Guzmans Log (names are generic).

    -- Enable Database Mail for this instance

    EXECUTE sp_configure 'show advanced', 1;

    RECONFIGURE;

    EXECUTE sp_configure 'Database Mail XPs',1;

    RECONFIGURE;

    GO

    -- Create a Database Mail account

    EXECUTE msdb.dbo.sysmail_add_account_sp

    @account_name = 'Primary Account',

    @description = 'Account used by all mail profiles.',

    @email_address = 'myaddress@mydomain.com',

    @replyto_address = 'myaddress@mydomain.com',

    @display_name = 'Database Mail',

    @mailserver_name = 'mail.mydomain.com';

    -- Create a Database Mail profile

    EXECUTE msdb.dbo.sysmail_add_profile_sp

    @profile_name = 'Default Public Profile',

    @description = 'Default public profile for all users';

    -- Add the account to the profile

    EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

    @profile_name = 'Default Public Profile',

    @account_name = 'Primary Account',

    @sequence_number = 1;

    -- Grant access to the profile to all msdb database users

    EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

    @profile_name = 'Default Public Profile',

    @principal_name = 'public',

    @is_default = 1;

    GO

    --send a test email

    EXECUTE msdb.dbo.sp_send_dbmail

    @subject = 'Test Database Mail Message',

    @recipients = 'testaddress@mydomain.com',

    @query = 'SELECT @@SERVERNAME';

    GO

    Then I run a number of queries to verify that the e-mail has been sent.

    The following is one of the queries that I run:

    SELECT items.subject,

    items.last_mod_date

    ,l.description FROM dbo.sysmail_faileditems as items

    INNER JOIN dbo.sysmail_event_log AS l

    ON items.mailitem_id = l.mailitem_id

    I hope this helps!

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Thanks for the reply.

    I have already removed all profile and accounts under the profile and recreate it ,but still have the same issue.

  • Try executing these queries:

    SELECT *

    FROM sysmail_mailitems

    GO

    GO

    SELECT *

    FROM sysmail_log

    GO

    SELECT *

    FROM sysmail_sentitems

    SELECT *

    FROM sysmail_allitems

    SELECT *

    FROM sysmail_event_log

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • We use database mail extensivly to report server information back to a monitoring environment. Typical issues we see are things like the mail server name is wrong or the SQL Service account can't use the mail server, or the mail server hasn't been configured to forward e-mail from the SQL Server.

    The issue is almost always on the mail server side, and I recommend you get the mail server log checked for errors related to incomming mail from your SQL server.

    Cheers

    Leo

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • In sysmail_sentitems,there is not any record.

    In sysmail_unsentitems,In includes all the test emails I have tried

    In sysmail_event_log does not show that DatabaseMail process has been started to stopped at all.

    The last two event in this table are(I manullay stopped and started the mail Queue):

    The mail queue stopped by login...

    The mail queue started by login...

  • For some reason it seems External program (DatabaseMail.exe) does not been run at all.And when I tried to manually run DatabaseMail.exe, nothing happened.

  • Did you try running opc.three's Scripts?

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • I created Account and Profile from the scratch and when I run the following query:

    SELECT items.subject,

    items.last_mod_date

    ,l.description FROM dbo.sysmail_faileditems as items

    INNER JOIN dbo.sysmail_event_log AS l

    ON items.mailitem_id = l.mailitem_id

    I didn't get any result.

  • When I tried to run the DatabaseMail.exe manually in Windows event log I face with the following error message:

    Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException

    Message: There was an error on the connection. Reason: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server), connection parameters: Server Name: ., Database Name: msdb

  • Sorry ,I guess I have missed something.What is OPC three script?

  • It was in the first response to your post - OPC.Three gave you a script to run

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • Does the server have anti-virus software on it?

    Some anti-virus services will block any email that tries to send from a server to protect it from being used to send spam. When I set up a SQL server to use Database Mail, I routinely have to ask our engineer in charge of anti-virus to turn off this blocking on on the server.

    Check the System & Application logs for messages about emails being blocked that correspond to your attempts to use Database Mail, and ask whoever configures anti-virus in your organization.

Viewing 15 posts - 1 through 15 (of 30 total)

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