April 26, 2010 at 1:27 am
Hi 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,
Dallas
April 26, 2010 at 4:48 pm
alen teplitsky (4/23/2010)
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.
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.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
April 27, 2010 at 7:57 am
could have been wrong in my recollection
a 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 fine
April 27, 2010 at 8:27 am
If 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.
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 27, 2010 at 8:39 am
the 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 everything
with newer hardware it's not an issue. it runs so fast there is time to rerun a process if something goes wrong
July 14, 2010 at 1:45 am
Hi ,
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 2
Object 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 2
Table 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 2
Object 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 2
Table 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).
July 14, 2010 at 6:37 am
Either 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.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
July 14, 2010 at 6:53 am
Thanks 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 .
Regards
Ashish Gupta
July 14, 2010 at 7:12 am
Most 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.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
July 14, 2010 at 7:39 am
ashish 10950 (7/14/2010)
Hi ,My database on running DBCC checkDB with noinfomsg , shows me the below error , please help what next to do ....
Please in future post stuff like this in a new thread. This thread is for discussions on the article only.
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
October 14, 2010 at 11:13 am
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 not 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 annually 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%5B/quote%5D
A couple of editorial issues, highlighted above, and here:
with the DATA_PURITY option before CheckDB will not fix this. <-- not??
[[cr]] <-- funky Carriage Return
The bad values have to annually updated <-- manually?
October 14, 2010 at 11:23 am
jpSQLDude (10/14/2010)
with the DATA_PURITY option before CheckDB will not fix this. <-- not??
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
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 default
CheckDB will not fix this.
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
February 27, 2011 at 3:17 pm
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.
__________________________
Allzu viel ist ungesund...
February 27, 2011 at 3:23 pm
Mr. Holio (2/27/2011)
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.
If I posted this in the wrong section pls move it to SQL 2K5 / Data Corruption forum topic. thank you.
__________________________
Allzu viel ist ungesund...
February 27, 2011 at 6:06 pm
Please 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.
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 - 46 through 60 (of 86 total)
You must be logged in to reply to this topic. Login to reply