Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

CheckDB Expand / Collapse
Author
Message
Posted Tuesday, August 18, 2009 2:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 9, 2012 7:25 AM
Points: 298, Visits: 107
We're still using SQL2000 so I got this wrong.
Post #772493
Posted Tuesday, August 18, 2009 3:48 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, August 1, 2014 8:09 AM
Points: 77, Visits: 167
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.
Post #772530
Posted Tuesday, August 18, 2009 9:55 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 3, 2014 11:47 AM
Points: 2,038, Visits: 1,664
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
Post #772856
Posted Tuesday, August 18, 2009 9:58 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 3, 2014 11:47 AM
Points: 2,038, Visits: 1,664
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
Post #772859
Posted Tuesday, August 18, 2009 10:16 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 8:53 AM
Points: 382, Visits: 1,143
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?


Sankar Reddy | http://SankarReddy.com/
Post #772866
Posted Tuesday, August 18, 2009 10:25 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, August 1, 2014 8:09 AM
Points: 77, Visits: 167
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.
Post #772874
Posted Tuesday, August 18, 2009 11:21 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:39 PM
Points: 3,901, Visits: 3,635
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
Post #772901
Posted Tuesday, August 18, 2009 12:24 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 3, 2014 11:47 AM
Points: 2,038, Visits: 1,664
@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
Post #772965
Posted Wednesday, August 19, 2009 2:55 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, August 1, 2014 8:09 AM
Points: 77, Visits: 167
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.
Post #773294
Posted Wednesday, August 19, 2009 7:24 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:39 PM
Points: 3,901, Visits: 3,635
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.
Post #773477
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse