Database Mail Queue will not stay on-line (INACTIVE)
Question
Monday, July 23, 2012 10:53 PM
Environment: Win2K8R2 SP1 EE\SQL2K8R2 SP1 SE (both x-64)
I used the Database Mail Configuration Wizard to set up database mail. When done, I sent a test e-mail and it worked without an issue. It is no longer working and I performed all the tests specified in the books on-line: Troubleshooting Database Mail. The mail items (4) are in the queue, but the queue status still shows as INACTIVE. I have also googled this, and one suggestion was to start and restart the instance. When I did this the queue went briefly to RECEIVES_OCCURRING and then right back to the INACTIVE status without sending out the queued up e-mails. Please advise.
Thanks and regards,
Andrew Smith, SQL Server DBA
All replies (7)
Friday, July 27, 2012 1:23 AM ✅Answered
Hi Andrew,
Glad you found the problem. I think the supported answer is to run the SQL Browser. You could also look into creating an alias (if cluster) with the port or putting an entry into your host file.
HTH,
Bill -- Microsoft CTS
Tuesday, July 24, 2012 6:48 AM
Hello Andrew,
If there are no mail to send, then the queue goes after some idle time into inactive state; as soon as a new mail comes in, it starts up again, there is no need to restart the SQL Server (that's never the solution).
Have you check if the mail are really in the active queue (unsent) and are not on error (failed)? Check also the mail log, see script below
SELECT * FROM msdb.dbo.sysmail_faileditems;
SELECT * FROM msdb.dbo.sysmail_unsentitems;
SELECT * FROM msdb.dbo.sysmail_event_log;
Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing
Tuesday, July 24, 2012 3:46 PM
Olaf: Thank you for your response. All four e-mails are in the unsentitems and there are no errors in the sysmail_enven_log. I tried sending another test e-mail this morning, and now there are five e-mails in the unsentitems. Regards,
Andrew Smith, SQL Server DBA
Wednesday, July 25, 2012 2:00 AM
Hi,
Here are some things to check. In your MSDB database query, "select * from sys.service_queues where name in ('ExternalMailQueue', 'InternalMailQueue'). Check if the is_activation_enabled, is_receive_enabled, and is_enqueue_enabled are all 1 (enabled). If not then use the alter queue command to enable activation and the queue. Run a profiler trace and and capture SP:StmtStarting, SP:StmtCompleted, all broker events, all errors and warnings. While profiler is running issue a test message, manually run the sp_sysmail_activate stored procedure, manually execute the databaseMail90.exe and review the profiler trace for any errors. Also pay attention if you see a broker:Activation events.
Hope this helps some,
Bill -- Microsoft CTS
Thursday, July 26, 2012 7:51 PM
Bill:
Thanks for your suggestions. After running the profiler and checking on the statements, I have finally isolated the problem. Now the question is how do i fix it! When the DatabaseMail.exe program runs, it is failing to connect to the instance. Here is the error details:
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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified), connection parameters: Server Name: SQLNN\SQLNI, Database Name: msdb
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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
The virtual network name SQLNN is valid as well as the instance name SQLNI. The only thing that I can think of is that between setting up database mail thru the wizard, the port number has been changed. The original installation was done using the default port of 1433. I believe I set up the database mail before I configured the port to a dynamic port of 51827. Any suggestions? Also, TCP, Shared Memory and Named Pipes are all enabled.
Andrew Smith, SQL Server DBA
Thursday, July 26, 2012 8:02 PM
Bill:
I was reading Books Online about default ports, and for a named instance using the SQL Server Browser port of 1434. Of course I had disabled this service as a standard practice. So just to test, I started the service and entered another test e-mail and eurika! I received all the e-mails. So now my question is how can I configure database mail so it will work without the Browser? Regards,
Andrew Smith, SQL Server DBA
Friday, July 27, 2012 5:23 PM
Bill:
Thanks again for all your help. :-) This was a clustered instance so I did create an alias (local) and added the port number and virtual server name. Then I configured the SQL Agent properties, Connections to use the alias and disabled the SQL Server Browser. Ran a test job and received the failure notification.
Andrew Smith, SQL Server DBA