March 13, 2009 at 10:40 am
When tried to compress table at page level it has thrown following error.
Server: Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: invalid encryption key. It occurred during a read of page (5:10701625) in database ID 34 at offset 0x00001469672000 in file 'K:\DEVARC2008\mssql\data\panarchive_0004_data_1.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). This error can be caused by many factors; for more information, see SQL Server Books Online.
Connection Broken
Upon exeucting dbcc check table got following error
Server: Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -9196902787720413184 (type Unknown), page (8192:2090495017). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 62916617 and -1.
Upon executing dbcc at page level It has given following information I am not able find where the problem is, any help in this regard is highly appreciated.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
PAGE: (31240:-2037373600)
BUFFER:
BUF @0x000000009EFA0A00
bpage = 0x000000009E3D4000 bhash = 0x0000000000000000 bpageno = (5:10701625)
bdbid = 34 breferences = 1 bUse1 = 57315
bstat = 0x2c00809 blog = 0x159a2159 bnext = 0x0000000000000000
PAGE HEADER:
Page @0x000000009E3D4000
m_pageId = (31240:-2037373600) m_headerVersion = 64 m_type = 50
m_typeFlagBits = 0xf0 m_level = 170 m_flagBits = 0xcc28
m_objId (AllocUnitId.idObj) = -654144992 m_indexId (AllocUnitId.idInd) = 516
Metadata: AllocUnitId = 145479692913213440 Metadata: PartitionId = 0
Metadata: IndexId = -1 Metadata: ObjectId = 0 m_prevPage = (8263:1158782176)
m_nextPage = (17409:-1406449656) pminlen = 27911 m_slotCnt = 275
m_freeCnt = 16415 m_freeData = 2863 m_reservedCnt = 16664
m_lsn = (-1770225408:168837572:52960) m_xactReserved = 23606
m_xdesId = (11264:1745436672) m_ghostRecCnt = 861 m_tornBits = 28061284
Allocation Status
GAM (5:10224640) = ALLOCATED SGAM (5:10224641) = NOT ALLOCATED
PFS (5:10700424) = 0x40 ALLOCATED 0_PCT_FULL DIFF (5:10224646) = NOT CHANGED
ML (5:10224647) = NOT MIN_LOGGED
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
March 13, 2009 at 3:13 pm
Have a read of http://www.sqlservercentral.com/articles/65804/.
But I think Paul or Gail might have to step in here ;-).
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 14, 2009 at 3:34 am
Please run a full checkDB and post the results here
DBCC CHECKDB (< Database Name > ) WITH NO_INFOMSGS, ALL_ERRORMSGS
Do you have a clean backup?
The 'invalid encryption key' confuses me. Are you running Transparent Database Encryption?
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
March 14, 2009 at 6:00 pm
Mohit (3/13/2009)
Have a read of http://www.sqlservercentral.com/articles/65804/.But I think Paul or Gail might have to step in here ;-).
Thanks.
I have gone through this article but the issue that I am into is different.
March 14, 2009 at 6:14 pm
GilaMonster (3/14/2009)
Please run a full checkDB and post the results here
DBCC CHECKDB (< Database Name > ) WITH NO_INFOMSGS, ALL_ERRORMSGSDo you have a clean backup?
The 'invalid encryption key' confuses me. Are you running Transparent Database Encryption?
Hi Gail,
I didn't run dbcc checkdb instead I ran DBCC checktable with options mentioned by you and gave this error
Server: 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.
Verified Tempdb space and there is ample free space. Space is not a constraint.
For your question "do you have clean backup", yes I verified backup file and it is good.
March 15, 2009 at 2:08 am
murthykalyani (3/14/2009)
I didn't run dbcc checkdb instead I ran DBCC checktable with options mentioned by you and gave this error
Please run a full checkDB. I want to make sure that there are no other errors in any other table before suggesting a repair strategy.
Server: 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.
You gave a different error in the previous post. Which is it?
If you got the previous error earlier and this error now, it looks like there's ongoing IO problems.
For your question "do you have clean backup", yes I verified backup file and it is good.
Good. By the looks of things, you may be restoring from the backup.
I have gone through this article but the issue that I am into is different.
Your issue is not different. The first error that you posted is referenced nearly exactly in the section "Corruption in the clustered index or heap" and the second error that you just posted is referenced exactly in the "Irreparable Corruption" section.
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
March 15, 2009 at 7:16 am
Gail,
Initially I got an error when compressing table on one of the file at page. I have posted that error.
Then I have run dbcc page on the page that I had issue to find out what's the problem couldn't find out as object_id it has shown as zero.
Ran DBCC on the table which is having issue and have posted as requested by you.
Other tables have no issues verified that.
I didn't run DBCC but this was run my other DBA friend and has sent results to our team
DBCC results for 'panarchive_0004'.
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.
DBCC results for 'sys.sysrscols'.
There are 1223 rows in 15 pages for object "sys.sysrscols".
DBCC results for 'sys.sysrowsets'.
There are 141 rows in 2 pages for object "sys.sysrowsets".
DBCC results for 'sys.sysallocunits'.
There are 153 rows in 4 pages for object "sys.sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 6 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.syspriorities'.
There are 0 rows in 0 pages for object "sys.syspriorities".
DBCC results for 'sys.sysfgfrag'.
There are 4 rows in 1 pages for object "sys.sysfgfrag".
DBCC results for 'sys.sysphfg'.
There are 2 rows in 1 pages for object "sys.sysphfg".
DBCC results for 'sys.sysprufiles'.
There are 6 rows in 1 pages for object "sys.sysprufiles".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysowners'.
There are 232 rows in 4 pages for object "sys.sysowners".
DBCC results for 'sys.sysprivs'.
There are 404 rows in 3 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 121 rows in 3 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscolpars'.
There are 2087 rows in 35 pages for object "sys.syscolpars".
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.syscerts'.
There are 0 rows in 0 pages for object "sys.syscerts".
DBCC results for 'sys.sysxprops'.
There are 0 rows in 0 pages for object "sys.sysxprops".
DBCC results for 'sys.sysscalartypes'.
There are 34 rows in 1 pages for object "sys.sysscalartypes".
DBCC results for 'sys.systypedsubobjs'.
There are 0 rows in 0 pages for object "sys.systypedsubobjs".
DBCC results for 'sys.sysidxstats'.
There are 160 rows in 3 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 394 rows in 3 pages for object "sys.sysiscols".
DBCC results for 'sys.sysbinobjs'.
There are 23 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for 'sys.sysaudacts'.
There are 0 rows in 0 pages for object "sys.sysaudacts".
DBCC results for 'sys.sysobjvalues'.
There are 204 rows in 90 pages for object "sys.sysobjvalues".
DBCC results for 'sys.sysclsobjs'.
There are 235 rows in 2 pages for object "sys.sysclsobjs".
DBCC results for 'sys.sysrowsetrefs'.
There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
DBCC results for 'sys.sysremsvcbinds'.
There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
DBCC results for 'sys.sysxmitqueue'.
There are 0 rows in 0 pages for object "sys.sysxmitqueue".
DBCC results for 'sys.sysrts'.
There are 1 rows in 1 pages for object "sys.sysrts".
DBCC results for 'sys.sysconvgroup'.
There are 0 rows in 0 pages for object "sys.sysconvgroup".
DBCC results for 'sys.sysdesend'.
There are 0 rows in 0 pages for object "sys.sysdesend".
DBCC results for 'sys.sysdercv'.
There are 0 rows in 0 pages for object "sys.sysdercv".
DBCC results for 'sys.syssingleobjrefs'.
There are 380 rows in 2 pages for object "sys.syssingleobjrefs".
DBCC results for 'sys.sysmultiobjrefs'.
There are 1434 rows in 14 pages for object "sys.sysmultiobjrefs".
DBCC results for 'sys.sysguidrefs'.
There are 0 rows in 0 pages for object "sys.sysguidrefs".
DBCC results for 'sys.syscompfragments'.
There are 0 rows in 0 pages for object "sys.syscompfragments".
DBCC results for 'sys.sysftstops'.
There are 0 rows in 0 pages for object "sys.sysftstops".
DBCC results for 'sys.sysqnames'.
There are 98 rows in 1 pages for object "sys.sysqnames".
DBCC results for 'sys.sysxmlcomponent'.
There are 99 rows in 1 pages for object "sys.sysxmlcomponent".
DBCC results for 'sys.sysxmlfacet'.
There are 112 rows in 1 pages for object "sys.sysxmlfacet".
DBCC results for 'sys.sysxmlplacement'.
There are 18 rows in 1 pages for object "sys.sysxmlplacement".
DBCC results for 'sys.sysobjkeycrypts'.
There are 0 rows in 0 pages for object "sys.sysobjkeycrypts".
DBCC results for 'sys.sysasymkeys'.
There are 0 rows in 0 pages for object "sys.sysasymkeys".
DBCC results for 'sys.syssqlguides'.
There are 0 rows in 0 pages for object "sys.syssqlguides".
DBCC results for 'sys.sysbinsubobjs'.
There are 3 rows in 1 pages for object "sys.sysbinsubobjs".
DBCC results for 'sys.syssoftobjrefs'.
There are 3 rows in 1 pages for object "sys.syssoftobjrefs".
DBCC results for 'EXTWebTraderData_0004'.
There are 7724861 rows in 82875 pages for object "EXTWebTraderData_0004".
DBCC results for 'EXTDealTags991_0004'.
There are 70649847 rows in 101732 pages for object "EXTDealTags991_0004".
DBCC results for 'Instrument_0004'.
There are 7724847 rows in 148039 pages for object "Instrument_0004".
DBCC results for 'EXTRioBackOffice_0004'.
There are 72723623 rows in 195927 pages for object "EXTRioBackOffice_0004".
DBCC results for 'EXTSACTradeDetail_0004'.
There are 72563703 rows in 246595 pages for object "EXTSACTradeDetail_0004".
DBCC results for 'InstBODetails_0004'.
There are 72731645 rows in 197987 pages for object "InstBODetails_0004".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'panarchive_0004'.
March 15, 2009 at 12:00 pm
I'm confused.
murthykalyani (3/13/2009)
Upon exeucting dbcc check table got following errorServer: Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -9196902787720413184 (type Unknown), page (8192:2090495017). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 62916617 and -1.
I ran DBCC checktable with options mentioned by you and gave this error
Server: 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.
Were those results from two different tables? Which tables were they?
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
March 15, 2009 at 12:10 pm
Double post. Nevermind.
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
March 15, 2009 at 4:02 pm
Both are from same tables.
Also today I refreshed the database and started compressing table at page level it has thrown same error.
Server: Msg 824, Level 24, State 2, Line 2
SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0xffffffff; actual signature: 0x93402813). It occurred during a read of page (5:10701625) in database ID 34 at offset 0x00001469672000 in file 'K:\DEVARC2008\mssql\data\panarchive_0004_data_1.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). This error can be caused by many factors; for more information, see SQL Server Books Online.
Connection Broken
March 15, 2009 at 4:17 pm
I think the best bet here is to restore that clean backup that you said you had and apply transaction log backups (if you have any of those) to get up to date. Of the errors you've posted, one is not repairable and the other will cause data loss if it's repaired.
Once you've restored, run a full checkDB to ensure that there is no corruption in the restored backup, check windows and hardware error logs for anything that may indicate a problem and then set up regular consistency checks so that you find the problems early.
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply