SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Consistency Error


Database Consistency Error

Author
Message
Sachin Diwakar
Sachin Diwakar
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 128
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...
GilaMonster
GilaMonster
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212021 Visits: 46259
Please run the following and post the full, complete and exact output.

DBCC CHECKDB (<Database Name>Wink 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


Sachin Diwakar
Sachin Diwakar
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 128
no dbcc running...
GilaMonster
GilaMonster
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212021 Visits: 46259
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


Sachin Diwakar
Sachin Diwakar
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 128
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.

Cool
GilaMonster
GilaMonster
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212021 Visits: 46259
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


avasilyev
avasilyev
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 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 :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search