Sql-server – Troubleshoot DBMail in Sql Server 2005

sql-server-2005

On a SQL Server 2005 machine, I have DBMail setup and correctly configured. I am able to use "Notify Operator" and EXEC msdb.dbo.sp_send_dbmail to queue emails.

But the problem is the queued mails never go through. I see 10 emails in the queue when I query

SELECT * FROM msdb.dbo.sysmail_unsentitems

But if I restart the SQL Server service, all mails go through and I receive emails in my inbox.

So far this is the only pattern I could find.

So my question is: is there a way to troubleshoot DBmail? I read some steps on MSDN, but they are not of much help. Any URLs to articles that could help are much appreciated.

Best Answer

Have you start the service broker on MSDB ? You need the service broker on to be able to process mail queue.

Read this post on Stackoverflow. An other link that may help you is how to troubleshoot a queue in sql. You can also use this link to get your service broker up and running on MSDB.

Hope that those links may help you.

Related Solutions

SQL Server – Query Running Differently on SQL 2005 vs SQL 2008 R2

I'd like you to rework the query.

You have sargability issues, and are even using scalar function calls in there, which will be hurting the query too. You may want to make a FullName computed column on Table2 and put an index on that, making sure your index INCLUDEs FirstName and LastName. You should also add indexes that help the other

Also, make an inline table-valued function to do your "RemoveNonLetter" functionality, and rework your query to use that, probably using APPLY as I've done here.

And definitely check that bug that Paul's answer refers to.

INSERT INTO Table1_GroupLock (iGroupID, dLockedDate)
SELECT 
 Table1.iGroupID,
 GETDATE()
FROM Table1
OUTER APPLY (SELECT NonLettersRemoved FROM dbo.ifnRemoveNonLetter(Table1.FullName)) AS fn (FullName)
OUTER APPLY (SELECT NonLettersRemoved FROM dbo.ifnRemoveNonLetter(Table1.FamilyName)) AS famn (FamilyName)
OUTER APPLY (SELECT NonLettersRemoved FROM dbo.ifnRemoveNonLetter(Table1.Child1Name)) AS c1n (Child1Name)
OUTER APPLY (SELECT NonLettersRemoved FROM dbo.ifnRemoveNonLetter(Table1.StepFamilyName)) AS sfn (StepFamilyName)
OUTER APPLY (SELECT NonLettersRemoved FROM dbo.ifnRemoveNonLetter(Table1.StepFamilyNameChild2)) AS sfnc2 (StepFamilyNameChild2)
WHERE 
 NOT EXISTS (
  SELECT 1
  FROM LinkedServer.Database.Table2 Alias2
  WHERE Alias2.FullName = fn.FullName
  UNION ALL
  SELECT 1
  FROM LinkedServer.Database.Table2 Alias2
  WHERE Alias2.FamilyName = famn.FamilyName AND Alias2.Child1Name = c1n.Child1Name
  UNION ALL
  SELECT 1
  FROM LinkedServer.Database.Table2 Alias2
  WHERE Alias2.StepFamilyName = sfn.StepFamilyName AND Alias2.StepFamilyNameChild1 = sfnc2.StepFamilyNameChild2
 ) 
 AND NOT EXISTS (
  SELECT 1
  FROM Table3
  INNER JOIN Table4
   ON Table4.FirstNameType = Table3.FirstNameType 
  INNER JOIN table5
   ON table5.LastNameType = Table3.LastNameType 
  WHERE 
   Table3.iGroupID = Table1.iGroupID
   AND Table3.bIsClosed = 0
   AND Table4.sNameTypeConstant = 'new_lastname'
   AND table5.sFirstNameConstant = 'new_firstname'
 )
;

Sql-server – Unable to get DBMail working on SQL Server 2005

I've seen this error when anti-virus is blocking database mail. With McAfee it is the Prevent Mass Mailing Worms rule. So you can check that databasemail.exe is in the exceptions list

Best Answer

Related Solutions

SQL Server – Query Running Differently on SQL 2005 vs SQL 2008 R2

Sql-server – Unable to get DBMail working on SQL Server 2005

Related Question