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 «««56789»»

Help, my database is corrupt. Now what? Expand / Collapse
Author
Message
Posted Wednesday, February 8, 2012 11:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 6:27 PM
Points: 2, Visits: 261
thank you for the link posted
Post #1249465
Posted Thursday, February 9, 2012 1:54 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:57 AM
Points: 43,045, Visits: 36,205
raj k (2/8/2012)
Great article.

Does a successful backup of database imply no corruptions in the Database?


Not at all.

If you're doing a normal backup (without checksum), then nothing is checked by the backup. If you do a backup with checksum, then the page checksums are recalculated and checked. The backup will fail if any pages with invalid checksums are found (only of use if the database is using checksums for page verify)

That said, the only way to be completely sure a DB is free of corruptions is to run CheckDB.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1249539
Posted Saturday, February 25, 2012 6:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, February 25, 2012 6:56 AM
Points: 1, Visits: 0
Here’s a great tool that lets you build any type of database apps for web and mobile fast and without coding http://www.caspio.com/
Post #1257769
Posted Monday, March 19, 2012 6:57 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:49 AM
Points: 2,404, Visits: 992
Added to the briefcase, nice insurance policy when the worst happens.

qh


SQL 2K acts like a spoilt child - you need to coax it round with lollipops.
Post #1268930
Posted Tuesday, December 11, 2012 2:52 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 7, 2014 10:11 AM
Points: 18, Visits: 71
Hi Gail,

First off, thanks for the great article and thanks to the SSC folks for making it so easy to find.

I've just started a new DBA job at a company and found some DBCC CHECKDB errors that fall into the second "non-serious" category you listed under Inaccurate Space Metadata.

In my case, the table with the issue happens to be a system table,

table 'sys.sysobjvalues' (object ID 60)

, and I wondered if that would impact (change) the answer you gave in any way.

Thanks,
Doug

DBCC output is below:

Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:75) in object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:117) in object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'sys.sysobjvalues' (object ID 60).
Post #1395335
Posted Tuesday, December 11, 2012 3:39 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:57 AM
Points: 43,045, Visits: 36,205
Could you start a new thread for your corruption problem please?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1395344
Posted Tuesday, December 11, 2012 7:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 7, 2014 10:11 AM
Points: 18, Visits: 71
Sorry - I probably asked my question in the wrong way.

What I really wanted to know was whether you would proceed differently (i.e., more cautiously) knowing the corruption affected a system table as opposed to a user table. Or is a table is a table is a table?

I wasn't really looking for a solution to my specific corruption problem. I've already written and tested a fix script (thanks to advice in your article and from Paul Randal's on SQLSkills.com).

I do see now that a lot of people have posted specific issues in this forum, and I will be careful to avoid that!

Thanks,
Doug
Post #1395395
Posted Tuesday, December 11, 2012 11:38 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:57 AM
Points: 43,045, Visits: 36,205
doug.baker-706021 (12/11/2012)
What I really wanted to know was whether you would proceed differently (i.e., more cautiously) knowing the corruption affected a system table as opposed to a user table.


Generally, yes. A system table cannot be repaired, cannot have indexes rebuilt. Typically corruption in a system table means restore from backup with few to no other options available.

Since the error you have isn't really corruption (just bad metadata), perhaps it's fixable. Not an error I've encountered in a system table before.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1395445
Posted Wednesday, December 12, 2012 11:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 7, 2014 10:11 AM
Points: 18, Visits: 71
Thank you, Gail. I appreciate the additional information.

Fixing this particular system table in this particular case appears to have had no adverse affects in the test environment, so I think your comment about it not really being corruption hits the point here. I think now I will go get my copy of SQL Server 2008 Internals and read the DBCC chapter by Paul Randal you mentioned in the footnote of your article, and ponder this further.

--Doug
Post #1395829
Posted Friday, December 28, 2012 4:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 12, 2014 3:07 PM
Points: 4, Visits: 78
I had a disk array go nuts recently and it corrupted MSDB and a an application DB. I could repair MSDB, and we had Full and Log backups for the application DB. Whew! We survived.

BUT! I have a question... I’ll do this in an outline so maybe it will be easier to follow:

1. Let's say that you have a application DB that gets fully backed up at 1:00am
2. It has transaction log backups every 15 minutes
3. The DB gets corrupted at, say, 6pm.
4. The corruption results in the loss of all the records in a user table
5. At 6:30pm, the application actually pulls off inserting records into the table
6. Your checkdb does not run until 8:00pm.

Is there a chance that the transaction log backup will be funky because of inserts (etc) into the corrupted table? To be specific, if there are no records in the table because of the corruption, that could result in inserts that would not have normally happened if there were records in the table. So, when you apply the log backup, it would try to insert duplicate records?

I am sure that I am ignorant of something (like something in the nature of log backups), but I have to ask!

Thanks,
M
Post #1401091
« Prev Topic | Next Topic »

Add to briefcase «««56789»»

Permissions Expand / Collapse