July 27, 2011 at 8:18 am
Hi Experts,
We got SCOM alert
SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0xffffffff; actual signature: 0x1580400f). It occurred during a read of page (6:20224851) in database ID 5 at offset 0x000026936a6000 in file 'F:\dba\Partition\db1.ndf '. 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).
CHECKDB returned error.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -8685639380888453120 (type Unknown), page ID (6:20284704) contains an incorrect page ID in its page header. The PageId in the page header = (29696:1761635328).
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -7027022372667850752 (type Unknown), page (121:7602281). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 29493257 and -1.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -6777520513750138880 (type Unknown), page (16640:1627419136). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 29493257 and -1.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -3191644460267798528 (type Unknown), page (114:6619252). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 29493769 and -1.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -940688895421251584 (type Unknown), page (52:3145748). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 29493769 and -1.
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 5 pages from (6:20220000) to (6:20228087). See other errors for cause.
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 5 pages from (6:20228088) to (6:20236175). See other errors for cause.
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 5 pages from (6:20236176) to (6:20244263). See other errors for cause.
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 5 pages from (6:20244264) to (6:20252351). See other errors for cause.
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 5 pages from (6:20252352) to (6:20260439). See other errors for cause.
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 5 pages from (6:20260440) to (6:20268527). See other errors for cause.
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 5 pages from (6:20268528) to (6:20276615). See other errors for cause.
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 5 pages from (6:20276616) to (6:20284703). See other errors for cause.
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 5 pages from (6:20284704) to (6:20292791). See other errors for cause.
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 5 pages from (6:20292792) to (6:20300879). See other errors for cause.
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 5 pages from (6:20300880) to (6:20308967). See other errors for cause.
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 5 pages from (6:20308968) to (6:20317055). See other errors for cause.
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 5 pages from (6:20317056) to (6:20325143). See other errors for cause.
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 5 pages from (6:20325144) to (6:20333231). See other errors for cause.
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 5 pages from (6:20333232) to (6:20341319). See other errors for cause.
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 5 pages from (6:20341320) to (6:20349407). See other errors for cause.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 7881797570461696 (type Unknown), page (2:6553710). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 29493769 and -1.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 9109462432612352 (type Unknown), page ID (6:20268528) contains an incorrect page ID in its page header. The PageId in the page header = (41115:1959692330).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 13680170917756928 (type Unknown), page ID (6:20228088) contains an incorrect page ID in its page header. The PageId in the page header = (69:2752632).
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 26177331742375936 (type Unknown), page (110:7274595). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 29493769 and -4.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 28660537608437760 (type Unknown), page ID (6:20341320) contains an incorrect page ID in its page header. The PageId in the page header = (20224:1946185728).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 29555306352803840 (type Unknown), page ID (6:20325144) contains an incorrect page ID in its page header. The PageId in the page header = (28575:-411979248).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 29788594362056704 (type Unknown), page ID (6:20317056) contains an incorrect page ID in its page header. The PageId in the page header = (29952:1124081747).
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 626845224113799168 (type Unknown), page (100:7274563). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 29493257 and -1.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 1153203267349512192 (type Unknown), page ID (6:20333232) contains an incorrect page ID in its page header. The PageId in the page header = (100:6881382).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 1153203340363694080 (type Unknown), page ID (6:20308968) contains an incorrect page ID in its page header. The PageId in the page header = (102:5177445).
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 7063448516110057472 (type Unknown), page (25856:1392517120). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 29493769 and -4.
CHECKDB found 32 allocation errors and 0 consistency errors not associated with any single object.
CHECKDB found 32 allocation errors and 0 consistency errors in database 'DB1'.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 6:20276928; actual 23810:6619235). It occurred during a read of page (6:20276928) in database ID 22 at offset 0x000026acd80000 in file 'F:\dba\Partition\db1.ndf :MSSQL_DBCC22'. 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.
Tried DBCC with REPAIR option and it didnt successed .What should i do now??
TIA
July 27, 2011 at 8:22 am
Don't do anything (especially do NOT do checkdb with allow dataloss. I'm calling in our best expert on the subject.
In the mean time, do you have a valid backup that does not contain this error?
July 27, 2011 at 8:47 am
Restore from a clean backup, that can't be repaired.
If you're in full recovery and have log backups going back to that clean backup, take a tail-log backup before starting the restore and you'll lose no data. Just be sure that the backup you use is clean. If you've been running regular CheckDB checks, you'll know at what point the database was fine.
Additionally, this doesn't just happen. Something's damaged the data file. I suggest you run some hardware diagnostics, something's written garbage over a large portion of the data file.
For anyone interested in the details, the lack of a listed minimum repair level means that there's at least one error that CheckDB can't fix. In this case, it's these:
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 5 pages from (6:20220000) to (6:20228087). See other errors for cause.
Those are both irreparable.
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 27, 2011 at 8:54 am
Darn, I guess you can't save them all :crying:.
July 27, 2011 at 8:57 am
Ninja's_RGR'us (7/27/2011)
Darn, I guess you can't save them all :crying:.
Lately it's been more a case of 'can't save any of em'. I must have had 10 or more irreparable corruption questions this month, between here and SQLTeam...
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 27, 2011 at 9:00 am
GilaMonster (7/27/2011)
Ninja's_RGR'us (7/27/2011)
Darn, I guess you can't save them all :crying:.Lately it's been more a case of 'can't save any of em'. I must have had 10 or more irreparable corruption questions this month, between here and SQLTeam...
I'll take that as a sign that people are getting better at solving this thanks to all your nice work in the past... better that than the alternative :hehe:.
July 27, 2011 at 9:39 am
Thanks Everyone.
Tried to restore from a clean backup and now its shwoing duplicate files and not allowing to delete the unneccesary file at all.
Tried deleteing the file making the DB offline and that throwed error Access Denied.
Please help.
July 27, 2011 at 9:40 am
Huh?
Drop the corrupt database (after taking a tail-log backup and verifying that the backup you're using really is clean), restore a clean backup.
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 27, 2011 at 9:41 am
GilaMonster (7/27/2011)
Restore from a clean backup, that can't be repaired.Additionally, this doesn't just happen. Something's damaged the data file. I suggest you run some hardware diagnostics, something's written garbage over a large portion of the data file.
Infrastructure team run chkdsk and they said they havent got anything.
July 27, 2011 at 9:43 am
What about SAN logs? Raid array logs? Windows event logs?
Have they checked that the drivers/firmware is up to date?
Chkdsk alone is not sufficient. Something corrupted your database, that something was most likely IO-related and if you don't find it and resolve it it's likely to happen again.
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 27, 2011 at 10:08 am
We restored the DB to different server and it created a duplicate file for one .ndf file.
We tried our best but not able to delete that file.
Do i need to raise SR with MS?
July 27, 2011 at 10:13 am
Ratheesh.K.Nair (7/27/2011)
We restored the DB to different server and it created a duplicate file for one .ndf file.We tried our best but not able to delete that file.
Do i need to raise SR with MS?
MS can't fix your hardware. I'd give a call to your SAN vendor first.
As a last ditch I'm make a new folder and do a restore with move and put all the files there. I can't see any way this could fail assuming valid backup and enough disk space.
July 27, 2011 at 10:34 am
Ratheesh.K.Nair (7/27/2011)
We restored the DB to different server and it created a duplicate file for one .ndf file.We tried our best but not able to delete that file.
Post your restore command.
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 27, 2011 at 11:21 am
Language Event 0 use [master] RESTORE database [db1] FROM VIRTUAL_DEVICE='VDI_D8B0897D-3BBC-412F-B7F3-AF854B8906C6_0', VIRTUAL_DEVICE='VDI_D8B0897D-3BBC-412F-B7F3-AF854B8906C6_1', VIRTUAL_DEVICE='VDI_D8B0897D-3BBC-412F-B7F3-AF854B8906C6_2' with blocksize=65536, maxtransfersize=1048576, buffercount=20, STATS = 10, MOVE N'db1' TO N'P:\MSSQL.10\Data\db1.mdf', MOVE N'db1_log' TO N'P:\MSSQL.10\Data\db1_log.ldf', MOVE N'db1_Indexes' TO N'P:\MSSQL.10\Data\db12.ndf', MOVE N'MemberProperties1' TO N'P:\MSSQL.10\Data\db13.ndf', MOVE N'SnapShot1' TO N'P:\MSSQL.10\Data\db14.ndf', MOVE N'db12' TO N'P:\MSSQL.10\Data\db15.ndf ', MOVE N'db1TypeId_Par_0_and_1.ndf' TO N'P:\MSSQL.10\Data\db16.ndf', MOVE N'db1TypeId_Par_2.ndf' TO N'P:\MSSQL.10\Data\db1_1.ndf', MOVE N'db1TypeId_Par_3.ndf' TO N'P:\MSSQL.10\Data\db1_2.ndf', MOVE N'sysft_FaqFullTextCatalog' TO N'P:\MSSQL.10\Data\db1'
July 27, 2011 at 11:25 am
Only a guess but it looks like the first and last files might be clashing with the same name (if the extension is auto-added by sql server)
RESTORE database [db1] FROM VIRTUAL_DEVICE=
'VDI_D8B0897D-3BBC-412F-B7F3-AF854B8906C6_0' , VIRTUAL_DEVICE=
'VDI_D8B0897D-3BBC-412F-B7F3-AF854B8906C6_1' , VIRTUAL_DEVICE=
'VDI_D8B0897D-3BBC-412F-B7F3-AF854B8906C6_2' with blocksize= 65536 ,
maxtransfersize= 1048576 , buffercount= 20 , STATS = 10 , MOVE N'db1' TO
N'P:\MSSQL.10\Data\db1.mdf' , MOVE N'db1_log' TO
N'P:\MSSQL.10\Data\db1_log.ldf' , MOVE N'db1_Indexes' TO
N'P:\MSSQL.10\Data\db12.ndf' , MOVE N'MemberProperties1' TO
N'P:\MSSQL.10\Data\db13.ndf' , MOVE N'SnapShot1' TO
N'P:\MSSQL.10\Data\db14.ndf' , MOVE N'db12' TO
N'P:\MSSQL.10\Data\db15.ndf ' , MOVE N'db1TypeId_Par_0_and_1.ndf' TO
N'P:\MSSQL.10\Data\db16.ndf' , MOVE N'db1TypeId_Par_2.ndf' TO
N'P:\MSSQL.10\Data\db1_1.ndf' , MOVE N'db1TypeId_Par_3.ndf' TO
N'P:\MSSQL.10\Data\db1_2.ndf' , MOVE N'sysft_FaqFullTextCatalog' TO
N'P:\MSSQL.10\Data\db1'
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply