Scenario
I've written a stress test that sends a 100 GenerateFile commands to the Bus.
I know the process has worked, because all 100 files have been created as expected.
Symptoms
In the Service logs however I find these warnings:
Warn | NServiceBus.Transport.SQLServer.ExpiredMessagesPurger | Purging expired messages from table [dbo].[TransportTable] failed after purging 0 messages.
And
Warn | NServiceBus.Transport.SQLServer.MessagePump | Purging expired messages from table [dbo].[TransportTable] failed with exception: System.Data.SqlClient.SqlException (0x80131904): You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.
Clarification: This occurs on the NServiceBus transport table, not a functional table of my own.
What I want
In the spirit of keeping the logs as clean as possible, I'd like to avoid the above warnings.
So
What is happening?
I've found this link, but the root cause is still not clear to me.
and
How can I avoid these warnings?
David Boike from Particular Software here.
Are you specifying a different transaction isolation level? You should be using READ COMMITTED. We actually have an open issue to only support read committed.
The READPAST hint is critical because if you have multiple endpoint instances processing messages from the same queue (i.e. table) then when it encounters a locked row, you don't want to hang waiting for that row to become available, because another instance is dealing with it. Instead you want to just skip over and look at the next row.
It looks like this warning is coming from the process that removes messages that have expired from use of a [TimeToBeReceived] attribute. You don't want that process getting hung up over locked rows either.