Home Forums SQL Server 2008 SQL Server 2008 - General DBmail shut down and how to audit. RE: DBmail shut down and how to audit.

  • January 19, 2017 at 2:43 pm

    #1923880

    anzz - Thursday, January 19, 2017 2:17 PM

    Thanks for the reply. 

    how to monitor DBmail, I mean in terms of audit if the DBmail is stopped.

    you missed the point above. it starts itself automatically when there's something int he queue, and turnns itself off after it is idle for a while.

    what you should audit is failed mail: items that are in the sent mail with a status that is not sent, and dig intot eh specific errors to find out wher ethe issue is.

    this is what i use; if it's been days or weeks since the last error, you are in good shape.
    SELECT top 100
    mail.send_request_date As SentDate,
    sent_status As Reason,
    err.[description],
    mail.*
    FROM [msdb].[dbo].[sysmail_allitems] mail
    inner join [msdb].[dbo].[sysmail_event_log] err
      ON err.mailitem_id = mail.mailitem_id
    WHERE mail.sent_status <> 'sent'
      order by mailitem_id desc

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!