Microsoft KB Archive/192489

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

Article ID: 192489

Article Last Modified on 3/2/2005

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q192489



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:

160181 : 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: kbbug kbpending KB192489

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.