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

• Microsoft SQL Server version 6.5

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