Database Consistency Error

  • Hi Folks,

    I am getting Consistency error with one of my database: I have tried all what I could but now I need some help on this.

    1. I have no last backup.

    2. Any DBCC, give me the same error (SQL ERROR 1)

    3. Running select give me the same error (SQL ERROR 2).

    4. I detached & reattached db from some differnet drive location, still error is there. No luck.

    5. Windows Event logs are full of errors (EVENT LOGS1)

    I dont think its IO/Hardware related because when I moved files from one drive to another, error still there and same. Looks like its database curruption, but I am not able to get into it.

    Any help will be appriciated.

    Thanks in advance.

    ------------------------------------------SQL ERROR1------------------------------------

    Msg 824, Level 24, State 2, Line 1

    SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:3280; actual 0:0). It occurred during a read of page (1:3280) in database ID 251 at offset 0x000000019a0000 in file 'D:\MSSQL\Data\dbfile.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.

    -------------------------------SQL ERROR2----------------------------------------------

    Msg 824, Level 24, State 2, Line 1

    SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:3280; actual 0:0). It occurred during a read of page (1:3280) in database ID 251 at offset 0x000000019a0000 in file 'D:\MSSQL\dbfile.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.

    -------------------EVENT LOGS1----------------------------------------------------------

    Event Type:Information

    Event Source:MSSQL$SERVER

    Event Category:(2)

    Event ID:2803

    Date:5/23/2010

    Time:5:13:24 AM

    User:USER

    Computer:SERVER

    Description:

    SQL Server has encountered 4 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

    For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

    Data:

    0000: f3 0a 00 00 0a 00 00 00 รณ.......

    0008: 14 00 00 00 53 00 54 00 ....S.T.

    0010: 41 00 44 00 42 00 30 00 A.D.B.0.

    0018: 30 00 37 00 39 00 5c 00 0.7.9.\.

    0020: 53 00 54 00 41 00 53 00 S.T.A.S.

    0028: 53 00 30 00 30 00 37 00 S.0.0.7.

    0030: 39 00 00 00 07 00 00 00 9.......

    0038: 6d 00 61 00 73 00 74 00 m.a.s.t.

    0040: 65 00 72 00 00 00 e.r...

  • Please run the following and post the full, complete and exact output.

    DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

    If you need urgent help, I suggest that you call Micrsoft's customer support and pay for their help. Forum replies are as and when the posters have time.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • no dbcc running...

  • Sorry, I don't understand what you meant by that last reply. Are you saying you can't run CheckDB, that you won't run checkDB or that it produces an error?

    If it produces an error, post it. I need to see exactly what that command returns before proceeding further.

    One quick question, I noticed a reference to DBID 251. Are there really over 200 databases on this instance?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • When I run any DBCC command, as I said, I got this error:

    ------------------------------------------SQL ERROR1------------------------------------

    Msg 824, Level 24, State 2, Line 1

    SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:3280; actual 0:0). It occurred during a read of page (1:3280) in database ID 251 at offset 0x000000019a0000 in file 'D:\MSSQL\Data\dbfile.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.

    Also, its true, that this instnace has more then 800 DBs of average a 1 GB of size. Its a big box.

    ๐Ÿ˜Ž

  • If you can't run checkDB, then there's going to be no way to repair this database (as checkDB is the thing that does the repair). Since you have no good backup (why not?) your options are pretty slim

    Extract the data that you can, script the objects, drop the database and recreate.

    Why are there no backups? I hope the same does not go for the other 800+ databases. Have you done consistency checks on all the others? It is a good idea. Also, do some checks of the IO subsystem as that is the likely cause. That you could move the file and still have the corruption does not rule out the IO subsystem. From the error, it looks like a portion of the file was zeroed out. (overwritten with 0).

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Based on this information "incorrect pageid (expected 1:3280; actual 0:0)" the page is empty (filled with zeroes). It could be file system corruption or failed sector on the drive. In our data recovery company we have this situation pretty often. Clients bringing corrupted SQL files where part of the file is empty or filled with information from other files. If you don't have backup there is possibility to scavenge the original drive and find lost pages.

    Open the mdf file in any hex viewer (e.x. WinHex) and go to offset 19A0000 and check the page for the data. It could be just page header corruption but more likely whole page will be empty. Then you can try to search the page by the page header on the disk and copy it to the file.

    Or send the drive to data recovery company ๐Ÿ™‚

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply