Microsoft KB Archive/171369

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

Article ID: 171369

Article Last Modified on 5/5/2006

-

APPLIES TO


 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q171369



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 error log:
 * 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, do not place the DELETE and INSERT operations in the same user-defined transaction.



STATUS
Microsoft has confirmed this to be a problem in SQL Server version 6.0 and 6.5. This problem has been corrected for version 6.5 in U.S. Service Pack 4 for Microsoft SQL Server version 6.5. For information about downloading and installing the latest SQL Server Service Pack, see http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=a6f79cb1-a420-445f-8a4b-bd77a7da194b.

For more information, contact your primary support provider.



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: kbbug kbfix kbusage KB171369

-

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

© Microsoft Corporation. All rights reserved.