Troubleshooting DB Mail Issues in SQL Server

Troubleshooting Database Mail issues in SQL Server-

Use the Database Mail Configuration Wizard, and try to send a test mail to investigate the point of failure.

Right click database mail –View database mail log to see error or we can SELECT * FROM msdb.dbo.sysmail_event_log.

Check the sent_Status column in the sysmail_allitems table.

The four values are sent, unsent, retrying and failed-

1)      If the status is sent and the recipients  hasn’t received the email yet, that the Database Mail external program successfully delivered the e-mail message to the SMTP server but it failed to deliver the message to the final recipient. At this point, the SMTP needs to be checked and fixed. (Engage the Exchange or Mail server team).

2)      If the status is unsent or retrying, it means that the Database Mail has not yet processed the e-mail message or is in the process of retrying after a failed attempt. This could be due to network conditions, volume of messages, SMTP server issues, etc. If the problem persists, use another profile or another mail host database.

3)      If the status is failed, it means that the Database Mail was unable to deliver the message to the SMTP server. Check the sysmail_log table and the destination address. Also be sure that there are no Network or SMTP issues.

Send a test email outside SQL Server and check if the recipients are receiving mails. If they do not receive then the problem is outside SQL Server.
Engage Exchange or other mail server teams to identify why we are not able to send emails to Office outlook or other mail clients.

If the mail has successfully reached to recipients, then the problem is with in SQL Server mail configuration.

Verify the following-

  1. Verify if Service Broker is enabled (select is_broker_enabled from sys.databases where name=‘MSDB’ (0 – disabled, 1- enabled).
    T-SQL – ALTER DATABASE MSDB SET ENABLE_BROKER
  2. Check if Database mail stored procedures are enabled (Surface Area Configuration >> “Surface Area Configuration for Features” >> Under MSSQLSERVER, expand Database Engine, and then click Database Mail. >> Ensure that Enable Database Mail stored procedures is selected, and then click Apply).
  3. Check if the user is part of DatabaseMailUserRole.
  4. Verify that the Database Mail executable is located in the correct directory – e.g. C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn and also verify, if ReadFromConfigurationFile is enabled.
  5. Verify that the service account for SQL Server has permission to run the executable, DatabaseMail90.exe, which requires network access to the SMTP servers specified in Database Mail accounts. Therefore, the service account for SQL Server must have permission to access the network, and the SMTP servers must allow connections from the computer that runs SQL Server.
  6. Verify the Anti-Virus scan properties, to see if ‘Mass-Mailing’ option is enabled.
    This can also create hindrance while transmitting mails from SQL Server.

Hope this helps.

Cheers!
Aman Kharbanda

Best practices for configuring SQL Server in Virtual Machines

Best practices and details for configuring MS SQL Server in Virtual machines-

RDBMS performance characteristics:

  • Traditionally CPU and Disk I/O intensive.
  • Require adequate amounts of CPU power to prevent SQL scheduler thread queuing.
  • Require fast I/O throughput to write logs without affecting query performance.
From a performance perspective, these issues arise:
  • CPU contention with other processes and virtual machines.
  • Disk I/O contention when writing logs or dealing with write-intensive database activity.
Allocate adequate CPU resources: 
  • Database performance suffers if CPU resources are constrained.
  • During levels of high database activity, run the DBCC SQLPERF (UMSSTATS) command against your database.
    This returns scheduler statistics for each CPU. If the num runnable metric, that is the Scheduler Queue Length is greater than 2, there may be CPU constraints, causing SQL Server threads to queue.
  • Consider increasing minimum and maximum CPU resource allocations to reduce this queue length.
Use uni-processor virtual machines. Upgrade to VSMP virtual machines only if necessary-
  • It is easy to move from a UP HAL to a SMP HAL, but not vice-versa.
  • Upgrading from UP to SMP mode requires a compulsive HAL change which can result in HLT related issues after the upgrade when using Win2k virtual machines.
  • Re-installation is required only when moving between ACPI and non-ACPI configurations. Virtual machines with two VCPUs require two PCPUs to be available to service a request. Systems with few PCPUs or many VSMP.
  • Virtual machines may see poor performance.
Separate data and logs on different physical disks:
  • A standard RDBMS best practice is to place SQL data and logs onto separate physical disks. This ensures that I/O intensive logging does not interfere with data queries.
  • In a virtual machine, create one (or more) .vmdk(s) for data and another for logs. Pre-allocate disk space when you create the virtual disks.
    Note: An auto-growing .vmdk sacrifices some performance for the added flexibility.
  • Put the .vmdk on a different LUN to spread activity across multiple spindles.

    This allows sequential writes to occur as fast as possible, helping to boost I/O performance.

Consider RAID 10 for write intensive databases:
  • RAID 10 is more efficient than RAID 5 for writes, but requires more disks.
  • Calculate your read/write ratio to determine if your database is write intensive.
  • After your database has been running for a few days, run Windows Task Manager and view the total number of I/O Read Bytes and I/O Write Bytes for the sqlservr.exe process.

    This shows you the ratio of SQL Server reads to writes since the SQL Server service was last restarted.

Disable screen savers and unused devices:

Summary:
  • Allocate adequate CPU resources.
  • Layout disks for maximum I/O throughput.

Hope this helps.

Cheers!!
Aman Kharbanda