BUG: LogReader Generates an AV on DELETE/INSERT of Text Columns

ID: Q192489


The information in this article applies to:


SYMPTOMS

The LogReader task generates an access violation (AV) when you have a single transaction that does a delete on a table with a text or image column followed by one or more inserts into the same table. This happens only when the DELETE/INSERT commands are inside a user-defined transaction and the deleted record had a valid value for the text or image column and the inserted record has a NULL value for the text or image column.


CAUSE

The LogReader tries to regenerate commands based on the transaction log entries. In this case, it tries to regenerate an UPDATE statement that would have the same effect as the DELETE/INSERT command and generates the AV in the process of reading the text or image value.


WORKAROUND

Avoid using explicit transactions on tables with text or image datatypes, when the deleted and inserted records would fit in the same page. The LogReader generates DELETE and INSERT commands if the inserted record does not fall in the same page as the deleted record. If you cannot avoid using explicit transactions, consider adding a fill factor (or lowering an existing one) on your clustered index so that the chances of the inserted record falling in the same page are reduced. This will reduce occurrences of this problem and may not avoid the problem completely.

You can also work around the problem by using an optional trace flag to make the LogReader generate DELETE/INSERT pairs for all updates. For additional information about the trace flag and command generation in LogReader, please see the following article in the Microsoft Knowledge Base:

Q160181 : INF: Trace Flag to Replicate UPDATE as DELETE/INSERT Pair


STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5.

Additional query words: prodsql av exception text regeneration replication


Keywords          : SSrvRep kbbug6.50 
Version           : WINNT:6.5
Platform          : winnt 
Issue type        : kbbug 

Last Reviewed: April 19, 1999