Problem

Messages are accepted by the EMF SMTP Relay, but are backing up in the Partition queue.

The following error is present in the log:

Event Type : Error
ID : 1719
Component : SMTP Relay
Description : Unable to remove a message from the store
Details :
Error code: 0x80040e31 in Return thread

Resolution

It is uncommon, but sometimes database administrators may change the "Miscellaneous" options for the EMFMail database when tweaking maintenance plan settings or in an attempt to harden the SQL DB.

It is important to know that the SMTP Relay uses many stored procedures that create, manipulate and delete indexes at all times and as such, the EMFMail options must be kept to their default settings.

The error above will be likely raised if the 'numeric_roundabort' setting is switched to ON. To fix the issue, you need to change this option, using the Microsoft SQL Server Management Studio and executing the following query:

USE master
GO
ALTER DATABASE EMFMail SET NUMERIC_ROUNDABORT OFF

More details:

When creating and manipulating indexes on computed columns or indexed views, the SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option NUMERIC_ROUNDABORT must be set to OFF.

If any of these options is not set to the required values, INSERT, UPDATE, DELETE, DBCC CHECKDB and DBCC CHECKTABLE actions on indexed views or tables with indexes on computed columns will fail. SQL Server will raise an error listing all the options that are incorrectly set. Also, SQL Server will process SELECT statements on these tables or indexed views as if the indexes on computed columns or on the views do not exist.