March 2, 2013 at 2:24 pm
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
March 3, 2013 at 7:16 am
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
March 3, 2013 at 9:55 am
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.
March 3, 2013 at 10:21 am
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/
March 3, 2013 at 2:59 pm
Thanks for the reply.
I have already removed all profile and accounts under the profile and recreate it ,but still have the same issue.
March 3, 2013 at 5:14 pm
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/
March 3, 2013 at 7:12 pm
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.
March 4, 2013 at 9:57 am
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...
March 4, 2013 at 9:59 am
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.
March 4, 2013 at 10:08 am
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/
March 4, 2013 at 10:11 am
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.
March 4, 2013 at 10:32 am
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
March 4, 2013 at 5:26 pm
Sorry ,I guess I have missed something.What is OPC three script?
March 4, 2013 at 7:36 pm
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
March 5, 2013 at 9:20 am
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