FIX: Delete/Insert on Table with Text/Image May Cause Error 38

 Last reviewed: December 19, 1997
Article ID: Q171369
The information in this article applies to:
  • Microsoft SQL Server, versions 6.0 and 6.5
BUG #: 15717 (6.00) 
       17010 (6.50)

SYMPTOMS

In a user-defined transaction, if a row is deleted and then followed immediately with an INSERT operation to a table containing one or more columns of a text or image datatype, the following error messages may be received by the client application: 

   Msg 5159, Level 16, State 1
   OS Error 38(Reached end of file.) on device 'C:\MSSQL\DATA\MASTER.DAT'
    during bufget
   The SQL Server is terminating this process.
In addition, the following errors may be recorded in the errorlog:
  • udread: Operating system error 38(Reached end of file.) on device 'C:\SQL60\DATA\MASTER.DAT' (virtpage 0x00ffff00).
  • mirrorproc: i/o error on primary device 'C:\SQL60\DATA\MASTER.DAT' Buffer 1352970 from database 'SMS' has page number 257 in the page header and page number -256 in the buffer header
  • udread: Operating system error 6(The handle is invalid.) on device 'C:\SQL60\DATA\MASTER.DAT' (virtpage 0x000005a4). WARNING: Process being freed while holding Dataserver semaphore

In SQL Server 6.5, SQL Server stops responding with a 100 percent CPU spin. This behavior was not observed under SQL Server 6.0.

WORKAROUND

To work around this problem, to not place the DELETE and INSERT operations in the same user-defined transaction.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server versions 6.0 and 6.5. This problem was corrected in the latest Microsoft SQL Server 6.5 U.S. Service Pack. For information on obtaining the service pack, query on the following word in the Microsoft Knowledge Base (without the spaces): 

   S E R V P A C K

MORE INFORMATION

The following code segment demonstrates the problem: 

   use SMS
   begin transaction
   delete from SiteControl where SiteCode = 'RDG' and TypeFlag = 1
   insert into SiteControl values ('RDG', 1, 0x0)
Please note that the problem does not occur every time this sequence of code is executed. In addition, error 38 may occur during database recovery if SQL Server was shut down and restarted after the problem first occurred.

Additional query words: Transact-SQL T-SQL tsql tran-sql hang hung locked
OS
Keywords : kbbug6.00 kbbug6.50 SSrvTran_SQL kbfix6.50.sp4 kbusage
Version : 6.0 6.5
Platform : NT WINDOWS
Issue type : kbbug
Solution Type : kbfix

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: December 19, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.