April 4, 2012 at 3:39 pm
I have a client (SQL 2008R2SP1) who run maintenance plans and reported back to me DBCC errors, unfortunately he's in SIMPLE mode, but the application has 2 week data-caching and the client has backups for several weeks.
I hopped on the server and has DBCC CheckDB ('DB NAME') WITH ALL_ERRORMSGS, NO_INFOMSGS and it returned no errors, I even restored a backup and the same thing.
The next step in the Maintenance Plan was a index reorg/rebuild (don't remember which), so this probably deallocated the corrupt bit.
My question is now that DBCC comes back clean, I know there's still corruption somewhere in there and it's just a matter of time before SQL reallocates to that page. Is there anything I can do in the mean time? Switch the Full mode? shrink the db and do a alter index rebuild? I know how to fix it, just curious cause it seems like you have to catch it in the bad state.
Also I know even I did catch it, DBCC PAGE wouldn't have worked from the backup because the pages were likely different from the rebuilt pages (post-backup rebuild).
Any advice would be appreciated!
/* Here's the errors, I know what they are though, post really isn't about that 🙂 */
Task start: 2012-04-01T00:30:06.
Task end: 2012-04-01T00:39:18.
Failed:(-1073548784) Executing the query "DBCC CHECKDB(N'FOODB') WITH NO_INFOMSGS " failed with the following error: "Object ID 1895013832, index ID 2, partition ID 72057594183090176, alloc unit ID 72057594179158016 (type In-row data): Page (1:563928) could not be processed. See other errors for details.
Table error: Object ID 1895013832, index ID 2, partition ID 72057594183090176, alloc unit ID 72057594179158016 (type In-row data). Page (1:563928) was not seen in the scan although its parent (1:226755) and previous (1:563903) refer to it. Check any previous errors.
Object ID 1895013832, index ID 2, partition ID 72057594183090176, alloc unit ID 72057594179158016 (type In-row data): Page (1:563929) could not be processed. See other errors for details.
Table error: Object ID 1895013832, index ID 2, partition ID 72057594183090176, alloc unit ID 72057594179158016 (type In-row data). Index node page (1:226755), slot 15 refers to child page (1:563929) and previous child (1:563928), but they were not encountered.
Object ID 1895013832, index ID 2, partition ID 72057594183090176, alloc unit ID 72057594179158016 (type In-row data): Page (1:563930) could not be processed. See other errors for details.
Table error: Object ID 1895013832, index ID 2, partition ID 72057594183090176, alloc unit ID 72057594179158016 (type In-row data). Index node page (1:226755), slot 16 refers to child page (1:563930) and previous child (1:563929), but they were not encountered.
Object ID 1895013832, index ID 2, partition ID 72057594183090176, alloc unit ID 72057594179158016 (type In-row data): Page (1:563931) could not be processed. See other errors for details.
Table error: Object ID 1895013832, index ID 2, partition ID 72057594183090176, alloc unit ID 72057594179158016 (type In-row data). Index node page (1:226755), slot 17 refers to child page (1:563931) and previous child (1:563930), but they were not encountered.
Object ID 1895013832, index ID 2, partition ID 72057594183090176, alloc unit ID 72057594179158016 (type In-row data): Page (1:563932) could not be processed. See other errors for details.
Table error: Object ID 1895013832, index ID 2, partition ID 72057594183090176, alloc unit ID 72057594179158016 (type In-row data). Index node page (1:226755), slot 18 refers to child page (1:563932) and previous child (1:563931), but they were not encountered.
Object ID 1895013832, index ID 2, partition ID 72057594183090176, alloc unit ID 72057594179158016 (type In-row data): Page (1:563933) could not be processed. See other errors for details.
Table error: Object ID 1895013832, index ID 2, partition ID 72057594183090176, alloc unit ID 72057594179158016 (type In-row data). Index node page (1:226755), slot 19 refers to child page (1:563933) and previous child (1:563932), but they were not encountered.
Object ID 1895013832, index ID 2, partition ID 72057594183090176, alloc unit ID 72057594179158016 (type In-row data): Page (1:563934) could not be processed. See other errors for details.
Table error: Object ID 1895013832, index ID 2, partition ID 72057594183090176, alloc unit ID 72057594179158016 (type In-row data). Index node page (1:226755), slot 20 refers to child page (1:563934) and previous child (1:563933), but they were not encountered.
Object ID 1895013832, index ID 2, partition ID 72057594183090176, alloc unit ID 72057594179158016 (type In-row data): Page (1:563935) could not be processed. See other errors for details.
Table error: Object ID 1895013832, index ID 2, partition ID 72057594183090176, alloc unit ID 72057594179158016 (type In-row data). Index node page (1:226755), slot 21 refers to child page (1:563935) and previous child (1:563934), but they were not encountered.
Table error: Object ID 1895013832, index ID 2, partition ID 72057594183090176, alloc unit ID 72057594179158016 (type In-row data). Page (1:563944) is missing a reference from previous page (1:563935). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 17 consistency errors in table 'FOOTABLE' (object ID 1895013832).
CHECKDB found 0 allocation errors and 17 consistency errors in database 'FOODB'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (FOO).". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
April 4, 2012 at 4:49 pm
dfrome (4/4/2012)
My question is now that DBCC comes back clean, I know there's still corruption somewhere in there and it's just a matter of time before SQL reallocates to that page.
No, there isn't still corruption in the DB. CheckDB comes back clean so the DB is clean. If the page that was corrupt is reallocated it will be rewritten as part of that reallocation. So whatever was wrong with it that triggered the corruption errors (and it wasn't 1 page, it was an entire extent) will be replaced by new page structures and new data.
Now, that page or any other page could be corrupted anew by whatever did the damage before, probably something in the IO subsystem, so you should check and make sure there are no lingering issues.
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
April 4, 2012 at 10:03 pm
GilaMonster (4/4/2012)
dfrome (4/4/2012)
My question is now that DBCC comes back clean, I know there's still corruption somewhere in there and it's just a matter of time before SQL reallocates to that page.No, there isn't still corruption in the DB. CheckDB comes back clean so the DB is clean. If the page that was corrupt is reallocated it will be rewritten as part of that reallocation. So whatever was wrong with it that triggered the corruption errors (and it wasn't 1 page, it was an entire extent) will be replaced by new page structures and new data.
Now, that page or any other page could be corrupted anew by whatever did the damage before, probably something in the IO subsystem, so you should check and make sure there are no lingering issues.
Gail,
Looking at the errors, isn't the corruption reported in a nonclustered index (index_id = 2), and that if you drop and recreate the nonclustered index wouldn't that fix the corruption?
April 4, 2012 at 10:14 pm
Yes, that's true, but i already knew how to fix it. Just wondering why they appeared and disappeared and i didn't drop/create. The only other thing that happened was a reorg (and that particular index didn't meet the criteria for a reorgan anyways). And to gail's point yes it was an entire extent (8 pages). I've always heard via paul r and even in some of gail's older posts not to do a reorganize after corruption cause it can mask errors. As i recall dbcc only checks allocated pages, so i figured the reorg deallocated them, bu they still existed till sql reallocated them. Not right i suppose, but still just irks me when things come and go.
April 5, 2012 at 6:04 am
Lynn Pettis (4/4/2012)
Looking at the errors, isn't the corruption reported in a nonclustered index (index_id = 2), and that if you drop and recreate the nonclustered index wouldn't that fix the corruption?
Yes, it is. I didn't say anything about how to fix it because it's already fixed. That said, the repair level listed is allow data loss and shouldn't be if all there errors are in index 2, also looks like there are some errors not in the listing.
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
April 5, 2012 at 6:10 am
dfrome (4/4/2012)
Yes, that's true, but i already knew how to fix it. Just wondering why they appeared and disappeared and i didn't drop/create. The only other thing that happened was a reorg (and that particular index didn't meet the criteria for a reorgan anyways).
REorg would not have made the corruption disappear, it would likely have encountered the corruption and failed. Rebuild could, as could anything else that deallocates pages (large deletes for example)
As i recall dbcc only checks allocated pages, so i figured the reorg deallocated them, bu they still existed till sql reallocated them. Not right i suppose, but still just irks me when things come and go.
No they don't still exist. It's like unallocated space on a drive, doesn't matter what was there, when a new file is put in that location it overwrites anything that was there before. So with a SQL page. When an unallocated page is allocated, it's rewritten with the new header, new data, new structure. It could become corrupt again if the IO subsystem mangles it again, but it won't be corrupt just from having been corrupt before being deallocated.
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
April 5, 2012 at 6:45 am
I copied the entire log from the agent job, as well as the SQLDUMP. I actually stumbled across another case where a customer reported the same thing (they have their own DBA, I hopped on the system and got the log), but no rebuild (table is just too big to do that even weekly on a 24/7 app). My db is ~100GB but 50GB is free, I didn't I though maybe when sql reorganized the allocation unit just moved from one block to another inside the "physical file", but i'm glad I don't have any. Here's another example where it says repair rebuild, but somehow it managed to fix itself, as when I ran with ALL_ERRORMSGS, it came back clean. Same index though (it's a really large table where about 50% of the db space resides). It still says repair_allow_data_loss...Actually I think it always says that, maybe they did that to just scare people 😉
DateTime: 2012-03-28 00:45:02
Database: [FOODB]
Status: ONLINE
Mirroring role: None
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Is accessible: Yes
Recovery model: SIMPLE
DateTime: 2012-03-28 00:45:02
Command: DBCC CHECKDB ([FOODB]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY
Msg 8928, Level 16, State 1, Server ServerNAME, Line 1
Object ID 872390177, index ID 2, partition ID 72057594087604224, alloc unit ID 72057594092912640 (type In-row data): Page (1:1923866) could not be processed. See other errors for details.
Msg 8976, Level 16, State 1, Server ServerNAME, Line 1
Table error: Object ID 872390177, index ID 2, partition ID 72057594087604224, alloc unit ID 72057594092912640 (type In-row data). Page (1:1923866) was not seen in the scan although its parent (1:3190686) and previous (1:1923865) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Server ServerNAME, Line 1
Table error: Object ID 872390177, index ID 2, partition ID 72057594087604224, alloc unit ID 72057594092912640 (type In-row data). Page (1:1923867) is missing a reference from previous page (1:1923866). Possible chain linkage problem.
Msg 8928, Level 16, State 1, Server ServerNAME, Line 1
Object ID 872390177, index ID 2, partition ID 72057594087604224, alloc unit ID 72057594092912640 (type In-row data): Page (1:1945586) could not be processed. See other errors for details.
Msg 8976, Level 16, State 1, Server ServerNAME, Line 1
Table error: Object ID 872390177, index ID 2, partition ID 72057594087604224, alloc unit ID 72057594092912640 (type In-row data). Page (1:1945586) was not seen in the scan although its parent (1:3500711) and previous (1:1945585) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Server ServerNAME, Line 1
Table error: Object ID 872390177, index ID 2, partition ID 72057594087604224, alloc unit ID 72057594092912640 (type In-row data). Page (1:1945587) is missing a reference from previous page (1:1945586). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 6 consistency errors in table 'FOOTABLE' (object ID 872390177).
CHECKDB found 0 allocation errors and 6 consistency errors in database 'FOODB'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (FOODB).
Outcome: Failed
Duration: 01:20:50
DateTime: 2012-03-28 02:05:52
=====================================================================
BugCheck Dump
=====================================================================
This file is generated by Microsoft SQL Server
version 10.0.2841.0
upon detection of fatal unexpected error. Please return this file,
the query or program that produced the bugcheck, the database and
the error log, and any other pertinent information with a Service Request.
Computer type is Intel(R) Xeon(R) CPU X5680 @ 3.33GHz.
Bios Version is INTEL - 6040000
PhoenixBIOS 4.0 Release 6.0
2 X64 level 8664, 3 Mhz processor (s).
Windows NT 6.0 Build 6002 CSD Service Pack 2.
Memory
MemoryLoad = 87%
Total Physical = 8190 MB
Available Physical = 1034 MB
Total Page File = 12221 MB
Available Page File = 5153 MB
Total Virtual = 8388607 MB
Available Virtual = 8376866 MB
DBCC RESULTS
--------------------
<DbccResults>
<Dbcc ID="0" Error="8928" Severity="16" State="1">Object ID 872390177, index ID 2, partition ID 72057594087604224
, alloc unit ID 72057594092912640 (type In-row data): Page (1:1923866) could not be processed. See other errors
for details.</Dbcc>
<Dbcc ID="1" Error="8976" Severity="16" State="1">Table error: Object ID 872390177, index ID 2, partition ID 7205
7594087604224, alloc unit ID 72057594092912640 (type In-row data). Page (1:1923866) was not seen in the scan alth
ough its parent (1:3190686) and previous (1:1923865) refer to it. Check any previous errors.</Dbcc>
<Dbcc ID="2" Error="8978" Severity="16" State="1">Table error: Object ID 872390177, index ID 2, partition ID 7205
7594087604224, alloc unit ID 72057594092912640 (type In-row data). Page (1:1923867) is missing a reference from p
revious page (1:1923866). Possible chain linkage problem.</Dbcc>
<Dbcc ID="3" Error="8928" Severity="16" State="1">Object ID 872390177, index ID 2, partition ID 72057594087604224
, alloc unit ID 72057594092912640 (type In-row data): Page (1:1945586) could not be processed. See other errors
for details.</Dbcc>
<Dbcc ID="4" Error="8976" Severity="16" State="1">Table error: Object ID 872390177, index ID 2, partition ID 7205
7594087604224, alloc unit ID 72057594092912640 (type In-row data). Page (1:1945586) was not seen in the scan alth
ough its parent (1:3500711) and previous (1:1945585) refer to it. Check any previous errors.</Dbcc>
<Dbcc ID="5" Error="8978" Severity="16" State="1">Table error: Object ID 872390177, index ID 2, partition ID 7205
7594087604224, alloc unit ID 72057594092912640 (type In-row data). Page (1:1945587) is missing a reference from p
revious page (1:1945586). Possible chain linkage problem.</Dbcc>
<Dbcc ID="6" Error="8990" Severity="10" State="1">CHECKDB found 0 allocation errors and 6 consistency errors in t
able 'FOOTABLE' (object ID 872390177).</Dbcc>
<Dbcc ID="7" Error="8989" Severity="10" State="1">CHECKDB found 0 allocation errors and 6 consistency errors in d
atabase 'FOODB'.</Dbcc>
<Dbcc ID="8" Error="8957" Severity="-1" State="1">DBCC CHECKDB (FOODB) WITH all_errormsgs, no_infomsgs, dat
a_purity executed by NT AUTHORITY\NETWORK SERVICE found 6 errors and repaired 0 errors. Elapsed time: 1 hours 20
minutes 46 seconds. Internal database snapshot has split point LSN = 00e2f247:0003b6eb:0001 and first LSN = 00e2
f247:0003b6ea:0001. This is an informational message only. No user action is required.</Dbcc>
<Dbcc ID="9" Error="8958" Severity="10" State="1">repair_allow_data_loss is the minimum repair level for the erro
rs found by DBCC CHECKDB (FOODB).</Dbcc>
</DbccResults>
**Dump thread - spid = 0, EC = 0x0000000100338080
***Stack Dump being sent to D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\LOG\SQLDump0001.txt
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 03/28/12 02:05:49 spid 61
*
* DBCC database corruption
*
* Input Buffer 158 bytes -
* EXECUTE [dbo].[DatabaseIntegrityCheck] @databases = 'USER_DAT
* ABASES'
*
* *******************************************************************************
* -------------------------------------------------------------------------------
* Short Stack Dump
CSession @0x00000000FF7343B0
----------------------------
m_spid = 61 m_cRef = 16 m_rgcRefType[0] = 1
m_rgcRefType[1] = 1 m_rgcRefType[2] = 13 m_rgcRefType[3] = 1
m_rgcRefType[4] = 0 m_rgcRefType[5] = 0 m_pmo = 0x00000000FF734080
m_pstackBhfPool = 0x0000000100339070 m_dwLoginFlags = 0x1081e0 m_fBackground = 0
m_eConnResetOption = 0 m_fUserProc = -1 m_fConnReset = 0
m_fIsConnReset = 0 m_fInLogin = 0 m_fAuditLoginSent = 1
m_fAuditLoginFailedSent = 0 m_fReplRelease = 0 m_fKill = 0
m_ulLoginStamp = 275232335 m_eclClient = 6 m_protType = 6
m_hHttpToken = FFFFFFFFFFFFFFFF
m_pV7LoginRec
---------------------
0000000000000000: cf000000 03000b73 00100000 00000007 bc0d0000 †Ï......s........¼...
0000000000000014: 00000000 e0811000 2c010000 09040000 5e000a00 †....à...,......^...
0000000000000028: 00000000 00000000 72000600 7e000000 00000000 †........r...~.......
000000000000003C: 7e000500 88000000 88000600 0050569c 02a99400 †~............PV?.©?.
0000000000000050: 3b009400 00000000 00000000 0000†††††††††††††††;.?...........
CPhysicalConnection @0x00000000FF734270
---------------------------------------
m_pPhyConn->m_pmo = 0x00000000FF734080
m_pPhyConn->m_pNetConn = 0x00000000FF734AD0
m_pPhyConn->m_pConnList = 0x00000000FF734380
m_pPhyConn->m_pSess = 0x00000000FF7343B0 m_pPhyConn->m_fTracked = -1
m_pPhyConn->m_cbPacketsize = 4096 m_pPhyConn->m_fMars = 0 m_pPhyConn->m_fKill = 0
CBatch @0x00000000FF734FA0
--------------------------
m_pSess = 0x00000000FF7343B0 m_pConn = 0x00000000FF734EA0 m_cRef = 3
m_rgcRefType[0] = 1 m_rgcRefType[1] = 1 m_rgcRefType[2] = 1
m_rgcRefType[3] = 0 m_rgcRefType[4] = 0 m_pTask = 0x00000000EC7702C8
EXCEPT (null) @0x0000000016DE6FF8
---------------------------------
exc_number = 0 exc_severity = 0 exc_func = 0x0000000001E2CCD0
Task @0x00000000EC7702C8
------------------------
CPU Ticks used (ms) = 4670257 Task State = 2
WAITINFO_INTERNAL: WaitResource = 0x0000000000000000 WAITINFO_INTERNAL: WaitType = 0x0
WAITINFO_INTERNAL: WaitSpinlock = 0x0000000000000000 SchedulerId = 0x0
ThreadId = 0xad0 m_state = 0 m_eAbortSev = 0
EC @0x0000000100338080
----------------------
spid = 0 ecid = 0 ec_stat = 0x0
ec_stat2 = 0x0 ec_atomic = 0x0 __fSubProc = 1
__pSETLS = 0x00000000FF734F10 __pSEParams = 0x00000000FF735350
SEInternalTLS @0x00000000FF734F10
---------------------------------
m_flags = 0 m_TLSstatus = 3 m_owningTask = 0x00000000EC7702C8
m_activeHeapDatasetList = 0x00000000FF734F10
m_activeIndexDatasetList = 0x00000000FF734F20 m_pDbccContext = 0x00000000F860A680
SEParams @0x00000000FF735350
----------------------------
m_lockTimeout = 3600000 m_isoLevel = 4096 m_logDontReplicate = 0
m_neverReplicate = 0 m_XactWorkspace = 0x00000000F9B8A080
m_pSessionLocks = 0x0000000100338980 m_pDbLocks = 0x00000000FF735860
m_execStats = 0x000000008049B190 m_pAllocFileLimit = 0x0000000000000000
April 5, 2012 at 8:20 am
dfrome (4/5/2012)
It still says repair_allow_data_loss...Actually I think it always says that, maybe they did that to just scare people
No it doesn't.
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
April 5, 2012 at 8:47 am
So do you think it's odd then given the errors? Thanks for your input on all this as usual you've been extremely helpful.
April 5, 2012 at 9:01 am
It is odd, and what's more odd is I don't see the error saying what's wrong with the page.
Normally if you have a single corrupt page in an index, you'll see these errors (in whatever order)
Error saying what's wrong with the page (eg page ID doesn't match, header checks failed, etc)
Error saying the page could not be processed, see other errors for details
Error saying that there's a missing reference to the next page
Error saying that there's a missing reference from the previous page
Error saying there's a missing reference from the parent.
This is an example (it is index 1 that's damaged, so this validly does require allow data loss)
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:158) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
Msg 8928, Level 16, State 1, Line 1
Object ID 2073058421, index ID 1, partition ID 72057594038386688, alloc unit ID 72057594042384384 (type In-row data): Page (1:158) could not be processed. See other errors for details.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 2073058421, index ID 1, partition ID 72057594038386688, alloc unit ID 72057594042384384 (type In-row data). Page (1:158) was not seen in the scan although its parent (1:154) and previous (1:157) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 2073058421, index ID 1, partition ID 72057594038386688, alloc unit ID 72057594042384384 (type In-row data). Page (1:159) is missing a reference from previous page (1:158). Possible chain linkage problem.
Now what I don't see in either of your outputs is the first of those. Could just be related to what exactly is wrong.
That said, if you've had 2 cases of corruption in the same table, you need to have a long look at the IO subsystem to see what could be causing it.
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
April 5, 2012 at 9:05 am
Same table, but two different servers (one is on a SAN) the other is just DAS RAID 10. It's not possible for an application to corrupt it is it? I would think SQL would jsut throw up on itself and not allow it. I mean we aren't doing any hex-editing here 😉 just inserts and reads.
April 5, 2012 at 9:15 am
dfrome (4/5/2012)
It's not possible for an application to corrupt it is it?
No. Not unless you've tripped over a SQL bug.
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
April 6, 2012 at 6:37 am
Have you done a bug request on connect or to Microsoft directly?
Although that won't solve your issue right now, having them to understand what has crash SQL (what has generated the dump files) could shed light on your issue.
If you can reproduce the issue with a "corrupted" database they will require the dumps files generated. They might request having the issue with trace flag 2544 enabled to get more data from the dump so be prepared it could be as large as what's SQL has in memory at that crash time.
April 16, 2012 at 11:50 am
So, the maintenance plan again generated errors, and again I had them rerun DBCC CHECKDB ('FOO') WITH NO_INFOMSGS, ALL_ERRORMSGS, and it came back clean.
I ran it again several times WITH NO_INFOMSGS and it still came back clean, and just normal old DBCC CheckDB. I'm having them rerun the maintenance plan now to see if that's the only place it is generated from, but this is just dang weird. Note this time it's the same table, but this time it's the clustered index (id 1), prior to that it was the non-clustered index. It's still 1 extent though.
This is just a whim, but it is possible that some in-memory corruption causes it, but it never gets written to disk? Maybe that's why it seems to fade in and out?
Microsoft(R) Server Maintenance Utility (Unicode) Version 10.50.1600 Report was generated on "ServerName".
Maintenance Plan: SV MaintenancePlan
Duration: 00:19:33
Status: Warning: One or more tasks failed.
Details:
Check Database Integrity (ServerName)
Check Database integrity on Local server connection Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All databases
Include indexes
Task start: 2012-04-16T00:30:06.
Task end: 2012-04-16T00:39:39.
Failed:(-1073548784) Executing the query "DBCC CHECKDB(N'FOO') WITH NO_INFOMSGS " failed with the following error: "Object ID 1895013832, index ID 1, partition ID 72057594183024640, alloc unit ID 72057594179092480 (type In-row data): Page (1:103768) could not be processed. See other errors for details.
Table error: Object ID 1895013832, index ID 1, partition ID 72057594183024640, alloc unit ID 72057594179092480 (type In-row data). Page (1:103768) was not seen in the scan although its parent (1:463529) and previous (1:103767) refer to it. Check any previous errors.
Object ID 1895013832, index ID 1, partition ID 72057594183024640, alloc unit ID 72057594179092480 (type In-row data): Page (1:103769) could not be processed. See other errors for details.
Table error: Object ID 1895013832, index ID 1, partition ID 72057594183024640, alloc unit ID 72057594179092480 (type In-row data). Index node page (1:463529), slot 42 refers to child page (1:103769) and previous child (1:103768), but they were not encountered.
Object ID 1895013832, index ID 1, partition ID 72057594183024640, alloc unit ID 72057594179092480 (type In-row data): Page (1:103770) could not be processed. See other errors for details.
Table error: Object ID 1895013832, index ID 1, partition ID 72057594183024640, alloc unit ID 72057594179092480 (type In-row data). Index node page (1:463529), slot 43 refers to child page (1:103770) and previous child (1:103769), but they were not encountered.
Object ID 1895013832, index ID 1, partition ID 72057594183024640, alloc unit ID 72057594179092480 (type In-row data): Page (1:103771) could not be processed. See other errors for details.
Table error: Object ID 1895013832, index ID 1, partition ID 72057594183024640, alloc unit ID 72057594179092480 (type In-row data). Index node page (1:463529), slot 44 refers to child page (1:103771) and previous child (1:103770), but they were not encountered.
Object ID 1895013832, index ID 1, partition ID 72057594183024640, alloc unit ID 72057594179092480 (type In-row data): Page (1:103772) could not be processed. See other errors for details.
Table error: Object ID 1895013832, index ID 1, partition ID 72057594183024640, alloc unit ID 72057594179092480 (type In-row data). Index node page (1:463529), slot 45 refers to child page (1:103772) and previous child (1:103771), but they were not encountered.
Object ID 1895013832, index ID 1, partition ID 72057594183024640, alloc unit ID 72057594179092480 (type In-row data): Page (1:103773) could not be processed. See other errors for details.
Table error: Object ID 1895013832, index ID 1, partition ID 72057594183024640, alloc unit ID 72057594179092480 (type In-row data). Index node page (1:463529), slot 46 refers to child page (1:103773) and previous child (1:103772), but they were not encountered.
Object ID 1895013832, index ID 1, partition ID 72057594183024640, alloc unit ID 72057594179092480 (type In-row data): Page (1:103774) could not be processed. See other errors for details.
Table error: Object ID 1895013832, index ID 1, partition ID 72057594183024640, alloc unit ID 72057594179092480 (type In-row data). Index node page (1:463529), slot 47 refers to child page (1:103774) and previous child (1:103773), but they were not encountered.
Object ID 1895013832, index ID 1, partition ID 72057594183024640, alloc unit ID 72057594179092480 (type In-row data): Page (1:103775) could not be processed. See other errors for details.
Table error: Object ID 1895013832, index ID 1, partition ID 72057594183024640, alloc unit ID 72057594179092480 (type In-row data). Index node page (1:463529), slot 48 refers to child page (1:103775) and previous child (1:103774), but they were not encountered.
Table error: Object ID 1895013832, index ID 1, partition ID 72057594183024640, alloc unit ID 72057594179092480 (type In-row data). Page (1:103776) is missing a reference from previous page (1:103775). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 17 consistency errors in table 'FooTable' (object ID 1895013832).
CHECKDB found 0 allocation errors and 17 consistency errors in database 'FOO'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (FOO).". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Command:USE [master]
GO
DBCC CHECKDB(N''master'') WITH NO_INFOMSGS
GO
USE [model]
GO
DBCC CHECKDB(N''model'') WITH NO_INFOMSGS
GO
USE [msdb]
GO
DBCC CHECKDB(N''msdb'') WITH NO_INFOMSGS
GO
USE [ReportServer]
GO
DBCC CHECKDB(N''ReportServer'') WITH NO_INFOMSGS
GO
USE [ReportServerTempDB]
GO
DBCC CHECKDB(N''ReportServerTempDB'') WITH NO_INFOMSGS
GO
USE [FOO]
GO
DBCC CHECKDB(N''FOO'') WITH NO_INFOMSGS
April 16, 2012 at 11:55 am
It's certainly possible. Extremely rare, but possible. I think I'm going to look for an expert to help out here...
Are you absolutely, 100% certain that there was no index rebuild between the time that the maintenance plan picked up the corruption and the time you ran checkDB? nothing that could deallocate lots of pages (truncate, delete, drop table, drop index, etc)
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 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply