August 17, 2009 at 11:57 am
bitbucket,
Not sure I am grasping your last post. Is it a question or statement?
Note that DBCC CHECKDB is re-written by Paul Randal in SQL Server 2005 but you are referring to a BOL link for SQL Server 2000.
This is where I take the criticism that the question could have been worded better.
[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]
August 17, 2009 at 1:03 pm
From Nov 2008 Books Online:
DBCC CHECKALLOC checks the allocation of all pages in the database, regardless of the type of page or type of object to which they belong. It also validates the various internal structures that are used to keep track of these pages and the relationships between them.
When you say this does not check statistics, what exactly isn't being checked? It doesn't resample the data and update statistics, but it does check the their logical and physical integrity.
August 17, 2009 at 1:09 pm
Scott Coleman (8/17/2009)
From Nov 2008 Books Online:DBCC CHECKALLOC checks the allocation of all pages in the database, regardless of the type of page or type of object to which they belong. It also validates the various internal structures that are used to keep track of these pages and the relationships between them.
When you say this does not check statistics, what exactly isn't being checked? It doesn't resample the data and update statistics, but it does check the their logical and physical integrity.
Scott,
It doesn't check the their logical and physical integrity of STATISTICS based on the connect item (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=158623&wa=wsignin1.0)
There are no references to this fact anywhere else except the connect item and that's what I tried to share with everyone with this quiz.
I have asked Paul Randal to shed some light on this one and hopefully if time permits and if he is interested will share some details.
[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]
August 17, 2009 at 1:46 pm
Sankar Reddy
Note that DBCC CHECKDB is re-written by Paul Randal in SQL Server 2005 but you are referring to a BOL link for SQL Server 2000.
Sankar - take my rants and raves as just that ... Now for the irony of it all .. I attended a user group meeting which featured Paul Randal of SQLSkills.com and his wife Kimberly Tripp as speakers last thursday evening. They put on an excellent 2 hour training session. Wish your question has been posted on Thursday or a day or a few before, and I could have discussed with him ... oh well. If you get a chance to talk with him, may I ask you to post what he concluded (If Paul agrees to the posting).
For all that has been posted in this forum, let me say your question made me think, and in particular that addition of statistics to the last selection was excellent. After all the question of the day is to make people think and learn and in that your question did a excellent job.
Further I hope that you will NOT stop submitting QODs, but bear with the comments from us nitpickers (those who find insignificant details of something unsatisfactory, often unjustifiably).
Again thanks for a good question.
August 17, 2009 at 2:57 pm
bitbucket,
Most of the comments on this post were constructive criticism, which I took nicely and Thanks for the kind words. I wish I could have wrote more questions on SSC but the publishing time is becoming too long. It almost took 6 months to publish this and that somehow dampens the spirit of writing more. I guess Steve Jones is getting lot of contributions for quiz questions and he must be doing the best to publish them in a timely fashion.
[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]
August 18, 2009 at 2:52 am
We're still using SQL2000 so I got this wrong.
August 18, 2009 at 3:48 am
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.
August 18, 2009 at 9:55 am
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
August 18, 2009 at 9:58 am
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
August 18, 2009 at 10:16 am
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 [:(] 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?
[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]
August 18, 2009 at 10:25 am
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 backupNo 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.
August 18, 2009 at 11:21 am
I would highly recommend you watch Paul Randal's TechED presentation.
August 18, 2009 at 12:24 pm
@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
August 19, 2009 at 2:55 am
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.
August 19, 2009 at 7:24 am
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.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply