none
torn-page issues with MSSQL2000 db attached to MSSQL2005 instance RRS feed

  • Question

  • I'm working on a project which involves the import of data in the form of an *.mdf file created externally using MSSQL2000 sp4.  The file is attached to our MSSQO2005 instance, & a number of procedures extract & transform the required data.

     

    We have recently noticed some problems reading data from the larger tables (2.5 M records) with error messages like:

    Msg 824, Level 24, State 2, Line 1
       SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x55555555; actual signature: 0x55575555). It occurred during a read of page (1:54042) in database ID 7 at offset 0x0000001a634000 in file 'myFile.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

     

    Running dbcc checkdb as advised gets an even scarier message:

    Msg 8967, Level 16, State 216, Line 1

    An internal error occurred in DBCC that prevented further processing. Contact Customer Support Services.

    DBCC results for 'myDbName'

    CHECKDB found 0 allocation errors and 0 consistency errors in database 'myDbName'.

     

    The creators of the imported file insist that it checks out without error at source.

    The same results are observed on three different hardware/os set-ups - it's clearly not a file io problem.

     

    ANy clues gratefully received.

     

    Ian


    Wednesday, February 13, 2008 1:27 PM

All replies

  • Have you upgraded the database from 80 to 90 compatibility?  I have never had that create a consistency problem, but you never know.  Also have you tried getting a secondary backup from the source and restoring that in to validate it wasn't just a problem with the transfer or media you got the first time?

     

    Wednesday, February 13, 2008 1:34 PM
    Moderator
  • A "torn page" error is a logical error in the database file.  It cannot be clean on the 2000 instance.  It is possible the version of 2000 is not detecting it.  What exact version are you and they running?  Make sure the 2000 they are running at least 8.00.2187 and yours is at least 9.00.2153.

    I would start by installing a 2000 8.00.2187 on your side.  Then attach the file and run checkdb.  If you see errors, that would prove the file was bad when you got it.

    Wednesday, February 13, 2008 1:45 PM
    Moderator
  • Thanks Tom,

     

    We are running 9.0.3042: the external source is apparently running 8.00.2039 - if it's critical we may be able to insist that they upgrade, but if we then still had the error our credibility would be shot!

    There is an intermediate point in the transfer trail, which I will get to see on Friday - it will be interesting to see if the error can be observed at that point.  Installing our own 2000 instance for testing may well depend on what we find out then.

     

    Thanks again,

    Ian

    Wednesday, February 13, 2008 2:12 PM
  • Thanks Jonathan,

     

    Changing the compatiblity level seems to make no difference.  I am persuing the question of whether the problem arises in transit.

     

    regards,

    Ian

     

    Wednesday, February 13, 2008 2:14 PM
  • The version 8.00.2039 is only SP4.  There are many hotfixes since that version which fix a number of issues.  I don't see your issue offhand, however the descriptions are not great.

    http://support.microsoft.com/kb/894905/

    I would highly suggest the 2000 server update to the current hotfixes.

    We transfer files from 2000 to 2005 using detach/attach on an almost daily basis and have not seen the issue you are seeing.  I think the problem lies on the 2000 SP4 box.

    If you install a 2000 SP4 and don't see the problem on it, then detach/attach and see the problem, try updating your 2000 to the current hotfixes and see what happens.

    Wednesday, February 13, 2008 5:39 PM
    Moderator
  • As mentioned earlier in this thread, a torn-page reflects that the page has incosistent data since it was last written by SQL Server. It points to a corruption in the IO code path outside of SQL Server. Was this .mdf file moved across multiple mediums before getting it to you? I doubt if this will matter, but have you tried attaching it to SQL2000/SP4 (same as the source database)?

    Monday, February 18, 2008 11:21 PM