We have some NServiceBus Handlers (6.4.0) using SQL Server Transport (3.1.2) that run fine, but their expired message purge cycle always fails to remove any rows, and a WARN is always logged about this. Contrary to the message, I don't see any messages accumulating in the endpoint table. The image below is of the handler running as a console app, logging the WARN.
WARN when running as console app
Environment Oddities: Our transport (and user data) database is in Compatibility Mode 80 (i.e. SQL Server 2000 mode) even though the server instance is 2008 R2. We had some trouble with the transport tables because the the server complained that ARITHABORT had to be on to support the index used on those tables, but our corporate software demands it be off by default. To get around changing it globally, in EndpointConfig we use the 'UseCustomConnectionFactory()' to supply a function which creates new SqlConnections and, after creation, runs SET ARITHABORT ON on the connection before returning it for use by the application. That seemed to solve that issue - but now we get the purge failure and WARN. The actual error message mentions "timeout" and 'server not responding' - but the database is continually available, query-able, and in use when this is occurring. Additionally, this occurs when volume is very low - as low as 2 or 3 messages per minute.
Any ideas on what might be wrong, how we might debug further, or how to resolve the issues, are very much appreciated.