SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Sankar Reddy / CheckDB / Latest Posts

RE: CheckDB

Cliff Jones — Wed, 19 Aug 2009 07:24:04 GMT

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.

RE: CheckDB

rja.carnegie — Wed, 19 Aug 2009 02:55:12 GMT

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.

RE: CheckDB

Paul Randal — Tue, 18 Aug 2009 12:24:39 GMT

@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

RE: CheckDB

Cliff Jones — Tue, 18 Aug 2009 11:21:10 GMT

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

RE: CheckDB

rja.carnegie — Tue, 18 Aug 2009 10:25:04 GMT

[quote][b]Paul Randal (8/18/2009)[/b][hr][quote][b]rja.carnegie (8/18/2009)[/b][hr]CHECKDB comes with the rather discouraging advice that you should address any errors by dropping the database and restoring from a good backup[/quote]No it doesn't - can you show me an example?[/quote]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" [i]is[/i] the problem.

RE: CheckDB

Sankar Reddy — Tue, 18 Aug 2009 10:16:57 GMT

[quote][b]Paul Randal (8/18/2009)[/b][hr]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.[/quote]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?

RE: CheckDB

Paul Randal — Tue, 18 Aug 2009 09:58:30 GMT

[quote][b]rja.carnegie (8/18/2009)[/b][hr]CHECKDB comes with the rather discouraging advice that you should address any errors by dropping the database and restoring from a good backup[/quote]No it doesn't - can you show me an example?

RE: CheckDB

Paul Randal — Tue, 18 Aug 2009 09:55:48 GMT

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.

RE: CheckDB

rja.carnegie — Tue, 18 Aug 2009 03:48:50 GMT

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 [i]negative[/i], 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.[code]Msg 8992, Level 16, State 1, Line 7Check 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.[/code]The database then probably passes CHECKDB, and although I've already declared I consider that [i]not[/i] 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.

RE: CheckDB

chriscoates — Tue, 18 Aug 2009 02:52:53 GMT

We're still using SQL2000 so I got this wrong.

RE: CheckDB

Sankar Reddy — Mon, 17 Aug 2009 14:57:12 GMT

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.

RE: CheckDB

bitbucket-25253 — Mon, 17 Aug 2009 13:46:18 GMT

[quote]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.[/quote]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.

RE: CheckDB

Sankar Reddy — Mon, 17 Aug 2009 13:09:39 GMT

[quote][b]Scott Coleman (8/17/2009)[/b][hr]From Nov 2008 Books Online:[quote]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.[/quote]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.[/quote]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.

RE: CheckDB

Scott Coleman — Mon, 17 Aug 2009 13:03:05 GMT

From Nov 2008 Books Online:[quote]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.[/quote]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.

RE: CheckDB

Sankar Reddy — Mon, 17 Aug 2009 11:57:22 GMT

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.

RE: CheckDB

bitbucket-25253 — Mon, 17 Aug 2009 11:45:17 GMT

Sankar Reddy[quote]5) It has a flag not to run against nonclustered indexes.FROM BOL: http://msdn.microsoft.com/en-us/library/ms176064.aspx-- Check the AdventureWorks database without nonclustered indexes.DBCC CHECKDB (AdventureWorks, NOINDEX);GOSo, it is true.[/quote]Repeating myself:[quote]From Books On LineDBCC CHECKDBRuns DBCC CHECKALLOC on the database.Runs DBCC CHECKTABLE on every table and view in the database.Runs DBCC CHECKCATALOG on the databaseFrom DBCC CHECKALLOC.http://technet.microsoft.com/en-us/library/aa258809(SQL.80).aspx[quote]Note NOINDEX is maintained for backward compatibility only. [b]All indexes are checked when executing DBCC CHECKALLOC.[/b][/quote]From Merriam-Webster Dictionary for ALL[quote]every member or individual component of [/quote]So then Sankar Reddy what you are saying is that DBCC CHECKDB when invoking DBCC CHECKALLOC passes to it a an undocumented parameter so that DBCC CHECKALLOC does NOT perform as it states in BOL

RE: CheckDB

Cliff Jones — Mon, 17 Aug 2009 11:27:41 GMT

Thanks for the explanation. I missed that 'Statistics' was in the last answer. When I first read the answers I thought you were saying that is would fail against Tempdb. I mis-read that also. Not enough coffee I guess.

RE: CheckDB

Sankar Reddy — Mon, 17 Aug 2009 11:13:30 GMT

First, I take the criticism that this could have been worded better. I wrote this probably 6-8 months ago but got published now so the context is NOT fresh off my mind.Check all that apply that are true about DBCC CHECKDB.1) It will fail when trying to run against tempdb database.FROM BOL: http://msdn.microsoft.com/en-us/library/ms176064.aspxRunning DBCC CHECKDB against tempdb does not perform any allocation or catalog checks and must acquire shared table locks to perform table checks. This is because, for performance reasons, database snapshots are not available on tempdb. This means that the required transactional consistency cannot be obtained.DBCC CHECKDB will run successfully against tempdb but may NOT perform all the checks as it does for other user databases. Note that it will NOT fail either. So, it is NOT true.2) It will fail when trying to run against the master database.FROM BOL: http://msdn.microsoft.com/en-us/library/ms176064.aspxwhen DBCC CHECKDB is executed against the master database, a second CHECKDB is also run internally on the Resource database. DBCC CHECKDB will run successfully against master. So, it is NOT true.3) It uses tempdb space.This is obvious and is true.4) It examines all indexes in the database including the disabled indexes.FROM BOL: http://msdn.microsoft.com/en-us/library/ms176064.aspxDBCC CHECKDB does not examine disabled indexes. For more information about disabled indexes, see Disabling Indexes.DBCC CHECKDB is re-written in SQL Server 2005 and will NOT check the disabled indexes. So, it is NOT true. 5) It has a flag not to run against nonclustered indexes.FROM BOL: http://msdn.microsoft.com/en-us/library/ms176064.aspx-- Check the AdventureWorks database without nonclustered indexes.DBCC CHECKDB (AdventureWorks, NOINDEX);GOSo, it is true.6) It is comprehensive and validates the logical and physical integrity of all the objects in the specified database including tables, indexes, indexed views & statistics.I deliberately wanted people to get this incorrectly and thats why I added STATISTICS at the end of the end sentence. I saw only one reference on this on the CONNECT item and wanted to share this with every one. Before I knew about the connect item, I didn't know that CHECKDB will NOT check the STATISTICS.

RE: CheckDB

Cliff Jones — Mon, 17 Aug 2009 10:00:14 GMT

Five people got the question correct so far, I am not sure how that happened.

RE: CheckDB

Scott Coleman — Mon, 17 Aug 2009 09:57:11 GMT

I hesitated to answer because I was positive at least one of the answers was going to have some wacky interpretation.DBCC CHECKDB does not fail when run against either master or tempdb, unless some other conditions are met. It MIGHT fail against any database, given a sufficient level of data corruption or hardware failure, but it is not true that it WILL fail against tempdb or master.From BOL under "DBCC CHECKDB":[quote]Checks the logical and physical integrity of [b][u]all[/u][/b] the objects in the specified database[/quote]I don't see any quibbles there about statistics. It may not refresh statistics, but it does check that they are correctly allocated.

RE: CheckDB

bitbucket-25253 — Mon, 17 Aug 2009 09:21:31 GMT

I firmly believe the author of this question did [b]NOT[/b] perform due diligence when selecting possible answers.The original statement [quote]Check all that apply that are true about [/quote] In the case of the master and temp db the question statement should have been: Check all that [b]may[/b] apply and are true about From Books On LineDBCC CHECKDB[quote]Runs DBCC CHECKALLOC on the database.Runs DBCC CHECKTABLE on every table and view in the database.Runs DBCC CHECKCATALOG on the database[/quote]From DBCC CHECKALLOC.[url]http://technet.microsoft.com/en-us/library/aa258809(SQL.80).aspx[/url][quote]Note NOINDEX is maintained for backward compatibility only. [b]All indexes are checked when executing DBCC CHECKALLOC.[/b][/quote]So in effect using DBCC CHECKDB which in turn invokes DBCC CHECLALLOC the correct answer is: [b]does check all indexes[/b]On the last possible answer also see:CHECK STATISTICS[url]http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=158623[/url](Bold facing entered by myself to drive home my arguement)

RE: CheckDB

Cliff Jones — Mon, 17 Aug 2009 09:13:49 GMT

So what kind of an error message does the author recieve when running CHECKDB agains the tempdb? I am not seeing it.

RE: CheckDB

Chad Crawford — Mon, 17 Aug 2009 08:07:03 GMT

[quote]I picked this option and it's the only reason I was wrong, be nice to know why that particular option is incorrect (I'm assuming it's the stats, but can't confirm that in BOL anywhere).[/quote]Same here - that is the only option I got wrong. I did find these links about stats after answering the QOD: [url=http://kbalertz.com/954467/receive-error-message-CHECKDB-statement-against-database-access-violation-occurs-query-table-database.aspx]Bug #: 443756[/url], [url=http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=158623]Bug 158623[/url]. They are both for 2K5, but probably still valid.

RE: CheckDB

jjtetzlo — Mon, 17 Aug 2009 07:54:46 GMT

If only 2% of people are getting this correct, it's a sign of a badly written question.

RE: CheckDB

Rob Goddard — Mon, 17 Aug 2009 07:08:01 GMT

[quote][b]Dietmar Weickert (8/17/2009)[/b][hr][quote]It is comprehensive and validates the logical and physical integrity of all the objects in the specified database including tables, indexes, indexed views & statistics.[/quote]What's wrong with this statement?[/quote]Ditto...I picked this option and it's the only reason I was wrong, be nice to know why that particular option is incorrect (I'm assuming it's the stats, but can't confirm that in BOL anywhere).For the few mentioning that DBCC CHECKDB does not fail on master or tempdb, you're right, but the way I read the question, it's not stating otherwise is it?

RE: CheckDB

Bill Mell — Mon, 17 Aug 2009 06:35:45 GMT

DBCC CHECKDB does NOT fail when run against tempdb.

RE: CheckDB

spaghettidba — Mon, 17 Aug 2009 06:30:44 GMT

DBCC CHECKDB runs againt master and tempdb with no errors. I wouldn't say it fails, I would say it doesn't behave exactly as you expect.

RE: CheckDB

Dietmar Weickert — Mon, 17 Aug 2009 04:32:46 GMT

[quote]It is comprehensive and validates the logical and physical integrity of all the objects in the specified database including tables, indexes, indexed views & statistics.[/quote]What's wrong with this statement?

RE: CheckDB

ChiragNS — Mon, 17 Aug 2009 02:07:39 GMT

The options you mentioned are not clear.[quote]It will fail when trying to run against tempdb database.It will fail when trying to run against the master database[/quote]DBCC CheckDB does not "fail" while running against master or tempdb databases.

CheckDB

Sankar Reddy — Sun, 16 Aug 2009 05:37:09 GMT

Comments posted to this topic are about the item [B]CheckDB[/B]