>You probably already saw this from your exchanges with Naomi (I agree Naomi, this is a good discussion topic).
>
>SQL Server runs through a different internal execution when dealing with VARCHAR(MAX) as opposed to VARCHAR(N). It's because SQL Server can't store/reference VARCHAR(MAX) as a straight/contiguous block of memory, since it can go up to 2 GB.
>
>I found a link here that talks about the difference in performance being anywhere from 10% to 25%.
>
>http://rusanu.com/2010/03/22/performance-comparison-of-varcharmax-vs-varcharn/
>
>Maybe the other factors you mentioned (corruption) might also apply, but wanted to share this link. It doesn't offer any kind of solution if you're required to use MAX, but it does illustrate the differences in a simple test.

There is no data corruption. As I blanked out the Task.Log field, saw the robot to execute the related tasks and populate the log again and this has ended in the same situation. I did it twice. So, because the Task.Log field was blanked out, it should rule out the possibilty of data corruption. Also, we have been given explanation that Varchar(MAX) is a factor in such situation. So, that is the reason of all this.

It is true I could change the field to be Varchar(50000), for example. As far as I understand, it seems to be related to the Varchar(MAX) only. As my log never goes over that, I should be ok. But, I would prefer to keep Varchar(MAX) in case one day it goes over. You know when you apply a fix length, one day or another, it will haunt you back.

My approach of using a flag field might do the trick. We are using that at other locations and it works well. And, a flag field can be indexed given us immediately which Task.Log field is having a value in it. So, when I'd do the SQL command, I'd rely on that field and the result would be instant. This should also release a lot of memory which is probably why I got those transport error situations because the content of those log fields to be handle in the SQL command is big, it has probably created that situation. It was easy for us to simulate the problem. From the Web interface, by simply launching several clicks to refresh the list was enough to end up with several errors in our Error table.