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 1234»»»

Problem with Database mail Expand / Collapse
Author
Message
Posted Saturday, March 2, 2013 2:24 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, August 11, 2014 6:32 PM
Points: 181, Visits: 731
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
Post #1425902
Posted Sunday, March 3, 2013 7:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:08 PM
Points: 7,097, Visits: 12,599
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
Post #1425943
Posted Sunday, March 3, 2013 9:55 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, August 11, 2014 6:32 PM
Points: 181, Visits: 731
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.
Post #1425971
Posted Sunday, March 3, 2013 10:21 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 3:55 PM
Points: 4,186, Visits: 4,264

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/

Post #1425975
Posted Sunday, March 3, 2013 2:59 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, August 11, 2014 6:32 PM
Points: 181, Visits: 731
Thanks for the reply.
I have already removed all profile and accounts under the profile and recreate it ,but still have the same issue.
Post #1426019
Posted Sunday, March 3, 2013 5:14 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 3:55 PM
Points: 4,186, Visits: 4,264
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/

Post #1426044
Posted Sunday, March 3, 2013 7:12 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 7, 2014 1:54 PM
Points: 450, Visits: 1,337
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
Post #1426056
Posted Monday, March 4, 2013 9:57 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, August 11, 2014 6:32 PM
Points: 181, Visits: 731
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...
Post #1426339
Posted Monday, March 4, 2013 9:59 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, August 11, 2014 6:32 PM
Points: 181, Visits: 731
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.
Post #1426341
Posted Monday, March 4, 2013 10:08 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 3:55 PM
Points: 4,186, Visits: 4,264
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/

Post #1426346
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse