May 23, 2010 at 3:56 am
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...
May 23, 2010 at 4:23 am
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
May 23, 2010 at 4:52 am
no dbcc running...
May 23, 2010 at 5:08 am
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
May 23, 2010 at 12:03 pm
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.
😎
May 23, 2010 at 10:45 pm
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
July 22, 2010 at 11:46 am
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