SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CheckDB


CheckDB

Author
Message
chriscoates
chriscoates
Old Hand
Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)

Group: General Forum Members
Points: 318 Visits: 110
We're still using SQL2000 so I got this wrong.
rja.carnegie
rja.carnegie
SSC-Enthusiastic
SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)

Group: General Forum Members
Points: 121 Visits: 169
Frankly, just asserting that CheckDB is a "comprehensive" database-validator had me reaching for No. One gotcha that I think I know is that after upgrade from SQL Server 2000 to 2005, which we just did here, table row usage / row count metadata (statistics?) partly reflects a looser maintenance standard and/or a different meaning/interpretation of values, and you should do DBCC UPDATEUSAGE on all non-system databases (at least). CHECKDB in 2005 will report usage considered wrong if it indicates that number of rows in a table is negative, otherwise not.

CHECKDB comes with the rather discouraging advice that you should address any errors by dropping the database and restoring from a good backup, but one apparent exception is when the error message tells you to run UPDATEUSAGE. We've also treated cases where the record of an object seemed to be inconsistent according to CHECKDB, by dropping and recreating the object. e.g.
Msg 8992, Level 16, State 1, Line 7
Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=2045250341,referenced_minor_id=1) of row (class=0,object_id=1161771196,column_id=0,referenced_major_id=2045250341,referenced_minor_id=1) in sys.sql_dependencies does not have a matching row (object_id=2045250341,column_id=1) in sys.columns.
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.


The database then probably passes CHECKDB, and although I've already declared I consider that not a guarantee of good data, we're living with it.

Oh, and with the ...WITH DATA_PURITY option we found some weird corrupted datetime values, something like
date 1969-08-18 (may be correct), time 1063:19:57.146. These can be displayed in SQL Server 2000's Query Analyzer, but they break connection if you try to inspect 'em in Management Studio, and of course cause other errors when you try to use them. The remedy is to remove the bad data using DELETE or UPDATE. I wonder how it got that way. The DATA_PURITY check is enabled as default on a database by running it once explicitly with no errors - SQL Server will go on including that test in CHECKDB - and is disabled by PHYSICAL_ONLY or by a database from SQL Server 2000 upgraded or restored onto SQL Server 2005.

When still using SQL Server 2000, we also just once saw a row holding NULL, or at least having the NULL flag set, in a not-nullable column.
Paul Randal
Paul Randal
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3651 Visits: 1717
ok - this isn't a good question. I wrote DBCC CHECKDB and I got the answer wrong. I guess it could be down to BOL stating that disabled indexes aren't checked - yes they are to an extent, as long as the pages in them are still allocated then they will be checked - and have to be. The nonclustered index cross-checks aren't done for them, but allocation checks etc are checked.

Nice twist with the stats on the last answer - CHECKDB has never checked the stats blob.

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
Paul Randal
Paul Randal
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3651 Visits: 1717
rja.carnegie (8/18/2009)
CHECKDB comes with the rather discouraging advice that you should address any errors by dropping the database and restoring from a good backup


No it doesn't - can you show me an example?

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
Sankar Reddy
Sankar Reddy
SSC-Addicted
SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)

Group: General Forum Members
Points: 496 Visits: 1250
Paul Randal (8/18/2009)
ok - this isn't a good question. I wrote DBCC CHECKDB and I got the answer wrong. I guess it could be down to BOL stating that disabled indexes aren't checked - yes they are to an extent, as long as the pages in them are still allocated then they will be checked - and have to be. The nonclustered index cross-checks aren't done for them, but allocation checks etc are checked.

Nice twist with the stats on the last answer - CHECKDB has never checked the stats blob.


Paul,

Thanks for responding to this. The information you shared above is only privy to you [Sad] and not public knowledge of whats checked against not checked for disabled indexes. BOL and your blog are the only sources of information. Can we expect a blog post on this and STATISTICS?

Sankar Reddy | http://SankarReddy.com/
rja.carnegie
rja.carnegie
SSC-Enthusiastic
SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)

Group: General Forum Members
Points: 121 Visits: 169
Paul Randal (8/18/2009)
rja.carnegie (8/18/2009)
CHECKDB comes with the rather discouraging advice that you should address any errors by dropping the database and restoring from a good backup


No it doesn't - can you show me an example?

Well, not dropping, but restoring from backup. Either way it's gone.
Online doc for 2008 at http://msdn.microsoft.com/en-us/library/ms176064.aspx goes:
"Important: Use the REPAIR options only as a last resort. To repair errors, we recommend restoring from a backup." There's more, but not contradicting that there.

Now I don't remember whether I tried "REPAIR" for the error I just described (on our new from-2000-to-2005 servers), before trying "identify, discard, and replace the object". Typically that's not been a table but a stored procedure or function that we don't even need to go to backup for, we have another copy elsewhere.

For anyone reading along, a tip for zipping through a long series of database CHECKDB logs in one Management Studio results window, looking for trouble: my old trick was that "Msg" only appears in the error messages, but in 2005 each CHECKDB generates several normal "Msg". So now I search the log for the word "level" with a space before and after. Of course you can suppress the informational, non-problem messages, but I prefer to run CHECKDB with minimal variations from vanilla version.

Our 2005 upgrade exercise was unusual; usually any problem we have is not a database fault at all, CHECKDB passes clean (which isn't exactly the same thing) - but it is wise to do it early in an investigation, in case "database broken" is the problem.
Cliff Jones
Cliff Jones
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4281 Visits: 3648
I would highly recommend you watch Paul Randal's TechED presentation.


http://www.sqlskills.com/BLOGS/PAUL/post/TechEd-80-minute-video-of-Corruption-Survival-Techniques-presentation.aspx
Paul Randal
Paul Randal
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3651 Visits: 1717
@Sankar Well, kind of - BOL doesn't give the internals of much of SQL Server at all.

@rja.carnegie Just because it says restore from a backup doesn't mean you lose your database. You never drop the database then restore it. What about restoring with a different name and looking to see if you can salvage data? What about performing a piecemeal restore (filegroup, file, page)? There are plenty of ways to not have to overwrite the database to recover from corruptions - so you're incorrect saying the database is gone.

To your other point, you should use WITH NO_INFOMSGS, ALL_ERRORMSGS to do what you describe - not sure why you don't want to run with those as it doesn't change the behavior one bit otherwise, and you're just giving yourself a bunch of extra work every time it runs.

Thanks

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
rja.carnegie
rja.carnegie
SSC-Enthusiastic
SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)

Group: General Forum Members
Points: 121 Visits: 169
Well, since you want a live application database to have no CHECKDB faults, and the recommended way to remove faults is to restore... Of course you can rename the "broken" database to stand next to the restored one, and usually you can get some or all of your data out of it. And ideally you arrange backup so that losing the live database is as un-painful as you can make it.

Also of course, by default the restored database uses the previous file names and paths. If you want something else, it's tricky.

I wrote a stored procedure to detach and rename or copy data files as well as (instead of) renaming a database, to get it out of the way of the restored database for instance, but it doesn't work on SQL Server 2005 yet - use of INFORMATION_SCHEMA.SCHEMATA is one thing wrong with it for 2005, I haven't found the others.
Cliff Jones
Cliff Jones
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4281 Visits: 3648
I have always been able to fix the broken database. I restore the backup with a new name and then drop and recreate any broken tables from the restored copy. In each case I have had the missing data after the repair was in the middle of the table and not recently added data. But you may also be able to restore the corrupt pages.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search