InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 19:54:58 GMT20http://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspx[quote][b]michael merrill (12/28/2012)[/b][hr]The idea that the table was actually corrupted (not by a user- but say a disk array error) and it still allowed inserts is the paranoid DBA in me talking! But just because you are paranoid, doesn’t mean... ;-)[/quote]Depending on how badly and what has been corrupted, the inserts may run fine or they may fail. If they run fine then the inserts will be logged normally (and hence present in log backups), if they fail, you get blatant error messages[quote]What is bothering me is that SQL does not tell you right away that there is DB corruption.[/quote]Sure it does. The instant that any query encounters corruption you get an error in the error log (823 or 824 are the more common). However, if you don't read the corrupt page, SQL has no way of intuiting that a page on disk that it has not read has been damaged by the IO subsystem.CheckDB, because it reads every page in the DB, will find all corrupt pages. Normal queries running against the DB might not use the pages that are damaged and hence will never notice that they're corruptSat, 29 Dec 2012 02:20:36 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspxYou wouldn't read the logs. You'd have the corrupt database, and the restore pre-corruption database and then you'd manually merge the data in the two tables.Fri, 28 Dec 2012 16:57:31 GMTPaul Randalhttp://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspxThanks Paul. That is what I thought.The idea that the table was actually corrupted (not by a user- but say a disk array error) and it still allowed inserts is the paranoid DBA in me talking! But just because you are paranoid, doesn’t mean... ;-)I have just seen DBs get corrupted, and they function just “fine”- But most likely not this sort of scenario.So you think that if a table had issues, SQL would not allow the inserts? If so, that is good. I would love a crash at that point instead of a silent failure.What is bothering me is that SQL does not tell you right away that there is DB corruption. I run CheckDB once a day on all the production servers, but I would much rather know in 30 seconds!What tool would you use to read the logs and merge the data back together in my hypothetical case?Again Thank you! This site has saved our bacon more than once.MFri, 28 Dec 2012 16:49:55 GMTmichael merrillhttp://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspxYes, potentially.You'd have to do the following:- extract all inserts performed after the table was emptied- restore the database to time just before the corruption occurred- manually merge in the post-corruption inserts, taking care of duplicates etcTedious.However, how would the corruption have removed all records in the table but still allow inserts? By 'corruption', do you mean 'someone accidentally deleted all the records'? That's the only way your scenario can occur IMHO.ThanksFri, 28 Dec 2012 16:23:59 GMTPaul Randalhttp://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspxI had a disk array go nuts recently and it corrupted MSDB and a an application DB. I could repair MSDB, and we had Full and Log backups for the application DB. Whew! We survived.BUT! I have a question... I’ll do this in an outline so maybe it will be easier to follow:1. Let's say that you have a application DB that gets fully backed up at 1:00am2. It has transaction log backups every 15 minutes3. The DB gets corrupted at, say, 6pm. 4. The corruption results in the loss of all the records in a user table5. At 6:30pm, the application actually pulls off inserting records into the table6. Your checkdb does not run until 8:00pm. Is there a chance that the transaction log backup will be funky because of inserts (etc) into the corrupted table? To be specific, if there are no records in the table because of the corruption, that could result in inserts that would not have normally happened if there were records in the table. So, when you apply the log backup, it would try to insert duplicate records?I am sure that I am ignorant of something (like something in the nature of log backups), but I have to ask!Thanks,MFri, 28 Dec 2012 16:11:39 GMTmichael merrillhttp://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspxThank you, Gail. I appreciate the additional information.Fixing this particular system table in this particular case appears to have had no adverse affects in the test environment, so I think your comment about it not really being corruption hits the point here. I think now I will go get my copy of SQL Server 2008 Internals and read the DBCC chapter by Paul Randal you mentioned in the footnote of your article, and ponder this further.--DougWed, 12 Dec 2012 11:45:24 GMTdoug.baker-706021http://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspx[quote][b]doug.baker-706021 (12/11/2012)[/b][hr]What I really wanted to know was whether you would proceed differently (i.e., more cautiously) knowing the corruption affected a system table as opposed to a user table. [/quote]Generally, yes. A system table cannot be repaired, cannot have indexes rebuilt. Typically corruption in a system table means restore from backup with few to no other options available.Since the error you have isn't really corruption (just bad metadata), perhaps it's fixable. Not an error I've encountered in a system table before.Tue, 11 Dec 2012 23:38:06 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspxSorry - I probably asked my question in the wrong way.What I really wanted to know was whether you would proceed differently (i.e., more cautiously) knowing the corruption affected a system table as opposed to a user table. Or is a table is a table is a table?I wasn't really looking for a solution to my specific corruption problem. I've already written and tested a fix script (thanks to advice in your article and from Paul Randal's on SQLSkills.com).I do see now that a lot of people have posted specific issues in this forum, and I will be careful to avoid that!Thanks,DougTue, 11 Dec 2012 19:43:34 GMTdoug.baker-706021http://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspxCould you start a new thread for your corruption problem please?Tue, 11 Dec 2012 15:39:14 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspxHi Gail,First off, thanks for the great article and thanks to the SSC folks for making it so easy to find.I've just started a new DBA job at a company and found some DBCC CHECKDB errors that fall into the second "non-serious" category you listed under Inaccurate Space Metadata.In my case, the table with the issue happens to be a system table,table 'sys.sysobjvalues' (object ID 60) , and I wondered if that would impact (change) the answer you gave in any way.Thanks,DougDBCC output is below:Msg 8914, Level 16, State 1, Line 1Incorrect PFS free space information for page (1:75) in object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL.Msg 8914, Level 16, State 1, Line 1Incorrect PFS free space information for page (1:117) in object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL.CHECKDB found 0 allocation errors and 2 consistency errors in table 'sys.sysobjvalues' (object ID 60).Tue, 11 Dec 2012 14:52:09 GMTdoug.baker-706021http://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspxAdded to the briefcase, nice insurance policy when the worst happens. :w00t:[i][b]qh[/b][/i]Mon, 19 Mar 2012 06:57:36 GMTquackhandle1975http://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspxHere’s a great tool that lets you build any type of database apps for web and mobile fast and without coding http://www.caspio.com/Sat, 25 Feb 2012 06:57:11 GMTkellyj481http://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspx[quote][b]raj k (2/8/2012)[/b][hr]Great article.Does a successful backup of database imply no corruptions in the Database?[/quote]Not at all. If you're doing a normal backup (without checksum), then nothing is checked by the backup. If you do a backup with checksum, then the page checksums are recalculated and checked. The backup will fail if any pages with invalid checksums are found (only of use if the database is using checksums for page verify)That said, the only way to be completely sure a DB is free of corruptions is to run CheckDB.Thu, 09 Feb 2012 01:54:24 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspxthank you for the link postedWed, 08 Feb 2012 23:00:36 GMTraj khttp://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspx[quote][b]raj k (2/8/2012)[/b][hr]Great article.Does a successful backup of database imply no corruptions in the Database?[/quote]No.See Phil Factor at [url=http://www.simple-talk.com/sql/database-administration/confessions-of-a-dba-my-worst-mistake/] http://www.simple-talk.com/sql/database-administration/confessions-of-a-dba-my-worst-mistake/[/url]Wed, 08 Feb 2012 21:54:01 GMTOl'SureHandhttp://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspxGreat article.Does a successful backup of database imply no corruptions in the Database?Wed, 08 Feb 2012 16:04:53 GMTraj khttp://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspxPlease either post a thread in the forums here (this is for discussion of the article, not fixing of a problem), or post a thread over at SQLTeam.Wed, 11 Jan 2012 11:46:16 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspxThank you for the information.Wed, 11 Jan 2012 11:23:46 GMTThomGhttp://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspxNo. If there's corruption, it'll find it in just about all cases.There are odd cases of single-bit corruption of data within a row where the page doesn't have checksum protection, but that's really unusual.Wed, 24 Aug 2011 08:36:50 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspxThanks Gail! Well, alternate storage is probably not an option for us because of budget reasons. 90% of objects in this DB can be dropped and recreated and repopulated within a 3 hr window(which is acceptable to users), the other 10% that can't be are copied over in a separate database everyday and that database is backed up. I have another question - any chances that DBCC CheckDB can fail to find corruption in user databases?Mon, 22 Aug 2011 15:25:24 GMTKangana Berihttp://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspxbtw, if that were my DB, I'd be trying to get it onto alternate storage first, before something worse corrupts.Mon, 22 Aug 2011 14:23:03 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspxThanks Gail! We will check and see what we find!Mon, 22 Aug 2011 13:55:35 GMTKangana Berihttp://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspx[quote][b]KB - 2000 (8/22/2011)[/b][hr]Anything specific that we should be checking on the I/O subsystem?[/quote]Everything.It could be anything in the IO stack, from anti-virus right down to the disks.Mon, 22 Aug 2011 13:35:16 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspxGreat article! We had 2 instances of data corruption in last 3 weeks and so here I am...The first instance was - "Unable to find index entry in index ID 4, of table 1211151360, in database 'dbname'. The indicated index is corrupt or there is a problem with the current update plan. Run DBCC CHECKDB or DBCC CHECKTABLE. If the problem persists, contact product support."Since it was a non-clustered index, I fixed it by dropping and recreating the index.The second instance - "Attempt to fetch logical page (1:2081816) in database 5 failed. It belongs to allocation unit 72057602053242880 not to 72057603070820352." The error occured during execution of a stored procedure and it was easy to find the problem query. The procedure creates a table A, inserts somes data in it by selecting from another table B, and then performs a update operation on the same table A. The error occured during the update. The fix was simple - I dropped and recreated the table A, insert the data from table B again and run the same update which succeeded this time.In both situations, the fixes were faster than running DBCC Checkdb and that is why I choose to do them. We do have regular DBCC CheckDB running.My question is: is it possible to prevent these errors from happening? In my situation- in the 2nd instance, the corruption occured soon after the table was created - was there any way of avoiding it? The article mentions that I/O subsystems are usually the reason behind corruptions. Anything specific that we should be checking on the I/O subsystem?Thanks!Mon, 22 Aug 2011 13:25:08 GMTKangana Berihttp://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspxPlease post a thread in the data corruption forum and include the complete output from a CheckDB with no_infomsgs, all_errormsgs.Check the SQL error log from the time that checkDB runs, you should be able to tell from what's logged which databases are affected.Sun, 27 Feb 2011 18:06:15 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspx[quote][b]Mr. Holio (2/27/2011)[/b][hr]Very useful article. However I've recently came across quite a few entries while checking my integrity check maintenance plan history:Executing the query "DBCC CHECKDB WITH NO_INFOMSGS " failed with the following error: "Object ID 82377223, index ID 1, partition ID 634556544600111, alloc unit ID 492818852566 (type In-row data): Errors found in off-row data with ID 11223557 owned by data record identified by RID = (1:888:2)What does this mean exactly? It's clear from the article that this is related to clustered indexes and obviously means data loss. My question is how to find out more what happened and why? And how to fix all this? I would say that there are at least hundred entries like that in the history. Also the plan do the integrity checks on around 100 DBs so no too sure how to identify the objects by ID in this case...?Thank you.[/quote]If I posted this in the wrong section pls move it to SQL 2K5 / Data Corruption forum topic. thank you.Sun, 27 Feb 2011 15:23:02 GMTMr. Holiohttp://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspxVery useful article. However I've recently came across quite a few entries while checking my integrity check maintenance plan history:Executing the query "DBCC CHECKDB WITH NO_INFOMSGS " failed with the following error: "Object ID 82377223, index ID 1, partition ID 634556544600111, alloc unit ID 492818852566 (type In-row data): Errors found in off-row data with ID 11223557 owned by data record identified by RID = (1:888:2)What does this mean exactly? It's clear from the article that this is related to clustered indexes and obviously means data loss. My question is how to find out more what happened and why? And how to fix all this? I would say that there are at least hundred entries like that in the history. Also the plan do the integrity checks on around 100 DBs so no too sure how to identify the objects by ID in this case...?Thank you.Sun, 27 Feb 2011 15:17:23 GMTMr. Holiohttp://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspx[quote][b]jpSQLDude (10/14/2010)[/b][hr][quote]with the DATA_PURITY option before CheckDB will [b]not[/b] fix this. <-- not??[/quote]Yup, that's correct.CheckDB with the data purity option will check for bad values, it will not fix them.There's half a sentence missing, that's why it looks odd. It should read[quote]These errors are not checked for by default on a database upgraded from SQL 2000 or lower. CheckDB must run successfully once, with the DATA_PURITY option before the errors will be checked for by defaultCheckDB will not fix this.[/quote]Thu, 14 Oct 2010 11:23:48 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspx[quote]These errors are not checked for by default on a database upgraded from SQL 2000 or lower. CheckDB must run successfully once, with the DATA_PURITY option before[[cr]]CheckDB will [b]not[/b] fix this. It doesn't know what values to put in the column to replace the invalid ones. The fix for this is fairly easy, but manual. The bad values have to [b]annually[/b] updated to something meaningful. The main challenge is finding the bad rows. This kb article goes over the steps in detail. http://support.microsoft.com/kb/923247[/quote]A couple of editorial issues, highlighted above, and here:with the DATA_PURITY option before CheckDB will [b]not[/b] fix this. <-- not??[[cr]] <-- funky Carriage ReturnThe bad values have to [b]annually[/b] updated <-- manually?Thu, 14 Oct 2010 11:13:34 GMTjpSQLDudehttp://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspx[quote][b]ashish 10950 (7/14/2010)[/b][hr]Hi ,My database on running DBCC checkDB with noinfomsg , shows me the below error , please help what next to do ....[/quote]Please in future post stuff like this in a new thread. This thread is for discussions on the article only.Wed, 14 Jul 2010 07:39:24 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspxMost likely your IO subsystem.With a heap it's very hard to tell what data will be lost - impossible for you as you don't have a clean backup.No, no alternative.Wed, 14 Jul 2010 07:12:06 GMTPaul Randalhttp://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspxThanks Paul , can you please tell me why these error has come , secondly how much data will be lost . I check the last backup that is also corrupt . Is there any other alternative except restoration or Repair_allow_data_loss by which i can save the data. I m not able to rebuid index either i tried it gave same error . RegardsAshish GuptaWed, 14 Jul 2010 06:53:10 GMTashish 10950http://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspxEither restore from your backups or run repair_allow_data_loss and you will lose a page of data from the Schedule table, with whatever effect that will have on your application.Wed, 14 Jul 2010 06:37:32 GMTPaul Randalhttp://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspxHi ,My database on running DBCC checkDB with noinfomsg , shows me the below error , please help what next to do ....Msg 8928, Level 16, State 1, Line 2Object ID 751341741, index ID 0, partition ID 72057594098155520, alloc unit ID 72057594101760000 (type In-row data): Page (1:76746) could not be processed. See other errors for details.Msg 8939, Level 16, State 98, Line 2Table error: Object ID 751341741, index ID 0, partition ID 72057594098155520, alloc unit ID 72057594101760000 (type In-row data), page (1:76746). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12584969 and -4.Msg 8928, Level 16, State 1, Line 2Object ID 751341741, index ID 2, partition ID 72057594098679808, alloc unit ID 72057594102480896 (type In-row data): Page (1:248976) could not be processed. See other errors for details.Msg 8939, Level 16, State 98, Line 2Table error: Object ID 751341741, index ID 2, partition ID 72057594098679808, alloc unit ID 72057594102480896 (type In-row data), page (1:248976). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 29362185 and -4.CHECKDB found 0 allocation errors and 4 consistency errors in table 'Schedule' (object ID 751341741).CHECKDB found 0 allocation errors and 4 consistency errors in database 'SPNDOKDP0001'.repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (SPNDOKDP0001).Wed, 14 Jul 2010 01:45:41 GMTashish 10950http://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspxthe worst we had was on SQL 2000 a few years ago. people noticed that data wasn't replicated to some subscribers. opened a case with PSS and they said to run checkdb on the publisher database after going through the normal checks. that was the one where management didn't want to take it down for hours. we ran checkdb on it on a saturday and it fixed everythingwith newer hardware it's not an issue. it runs so fast there is time to rerun a process if something goes wrongTue, 27 Apr 2010 08:39:33 GMTalen teplitskyhttp://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspxIf the index was corrupt then, depending on the type and severity of corruption, a drop/recreate of the index could well have fixed it or simply deallocated the corrupt page. That does and can happen. It's one of the causes of 'disappearing' corruption.Tue, 27 Apr 2010 08:27:01 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspxcould have been wrong in my recollectiona month or two ago we had an instance where an update job failed. it was a simple update that joined data from another server/database. Restored the full and diff backups in QA, ran dbcc and an index was corrupt. during that process i dropped and recreated that index in production and the update ran fine. maybe we didn't run dbcc in production that time.the last time we ran it we had an issue where a database that was replicated between SAN volumes didn't mount on the backup server. turned out the issue was the 64k offset. i set it for a new disk volume in production which was win 2003 x64. the backup server is win 2000. ran dbcc in production and QA and everything checked out. rebuilt the disk volume without the 64k offset and it worked fineTue, 27 Apr 2010 07:57:34 GMTalen teplitskyhttp://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspx[quote][b]alen teplitsky (4/23/2010)[/b][hr]good articlein my experience the biggest impediment to running checkDB on a regular basis is management. i've seen it run without the repair or the all info messages options and it won't pick up any corruption. once you run it with those options it picks up corruption if there is any. but the performance/blocking hit is not popular with management.[/quote]Shouldn't be possible on 2005 onwards, but I know why if you've seen it on 2000 - the threading algorithm is different when repair is specified so may allow deeper checks to be performed (all_infomsgs cannot possibly produce the effect you describe). Could also be that there wasn't any corruption on the first pass - and there was on the second. Random coincidence.Mon, 26 Apr 2010 16:48:11 GMTPaul Randalhttp://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspxHi alan,Interesting point you have. Please enlighten me on a few steps in your process:1. Did you mean that you restored the database to a spare server (vmware?) from the latest (working) backup, or, run an instant backup on the current DB and restore it to the spare box.2. do you mean to say that in your maintenance procedure, you restore backups, then run a checkdb to test database consistency (and also, the backup/restore ops are working).3. What normally do we do with the production system while a check like that is performing.. continue OLTP? set it read only? This maybe a newbie question, thus showing my experience level :) Thanks,DallasMon, 26 Apr 2010 01:27:51 GMTdallas-1069889http://www.sqlservercentral.com/Forums/Topic657263-1390-1.aspxGreat article Gail! I'm glad it was reprinted today because I missed it when it first came out. Thanks. :-)Fri, 23 Apr 2010 09:39:33 GMTTrey Staker