Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database Consistency Error Expand / Collapse
Author
Message
Posted Sunday, May 23, 2010 3:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 1, 2014 3:10 PM
Points: 22, Visits: 96
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...
Post #926497
Posted Sunday, May 23, 2010 4:23 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:09 AM
Points: 42,493, Visits: 35,563
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 2008, MVP
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

Post #926499
Posted Sunday, May 23, 2010 4:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 1, 2014 3:10 PM
Points: 22, Visits: 96
no dbcc running...
Post #926500
Posted Sunday, May 23, 2010 5:08 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:09 AM
Points: 42,493, Visits: 35,563
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 2008, MVP
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

Post #926502
Posted Sunday, May 23, 2010 12:03 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 1, 2014 3:10 PM
Points: 22, Visits: 96
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.




Post #926540
Posted Sunday, May 23, 2010 10:45 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:09 AM
Points: 42,493, Visits: 35,563
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 2008, MVP
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

Post #926609
Posted Thursday, July 22, 2010 11:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 26, 2012 8:58 AM
Points: 1, Visits: 3
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
Post #957410
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse