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


My SQL Server Database is Corrupt - Now What?!


My SQL Server Database is Corrupt - Now What?!

Author
Message
Paul Randal
Paul Randal
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15630 Visits: 1734
Comments posted to this topic are about the item My SQL Server Database is Corrupt - Now What?!

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
Ness
Ness
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2120 Visits: 974
Hi,

Would you run the DBCC CHECKDB with the REPAIR_REBUILD option (after recovery from a backup/HA solution)? I seem to remember that this was the preferred option the MS 2005 / 2008 MCITP learning. I know that real life can be far detached from the learning, or are you attempting to fix any Ix's individually?

SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
manik_anu
manik_anu
SSC Eights!
SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)

Group: General Forum Members
Points: 956 Visits: 319
hi all,
i have deleted the tables in a database n particular sequence. after that the database name has changes as <databasename(suspect)>. What did it means? the database corrupted??? if it correpts how can i recover??? DBCC CHECKDB will useful to this.....?????

Manik
You cannot get to the top by sitting on your bottom.
Ness
Ness
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2120 Visits: 974
Try recovering from a backup first and then if that does not work try the following : http://www.sqlskills.com/blogs/paul/creating-detaching-re-attaching-and-fixing-a-suspect-database/

SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
SALIM ALI
SALIM ALI
SSC-Addicted
SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)

Group: General Forum Members
Points: 487 Visits: 250
Failover, Restore and Repair
I think one has been forgotten, and it's one that's not to be taken likely because you have to know your data. This method is Reconstruction.

Sometimes, in the event of only having an old backup, it is possible to reconstruct the data in certain circumstances. I had a table corruption s few years back where the only backup I could use for recovery was 3 weeks old. {edit} This also had the corrption so I had to go back to an earlier tape copy {end edit} You can usually guarantee that if something is going to go wrong, it will go wrong when you're on leave and no-one will pick it up, leaving it for you to deal with upon return.

The corruption was on the clustered index. I was unable to drop it as I kept getting errors. The clustered index ordered the data sequentially, so all the historic stuff was at the head of the table with the new data at the tail end. The corruption was somewhere in the middle (dated prior to the last good backup, good!)..

DBCC CHECKDB told me where the corruption was.

DBCC IND ('trapezedata', {tablename},-1)
provided me with a list of the PageIds for the corrupted table

I used these to fuel the next DBCC command...

DBCC TRACEon(3604)
DBCC PAGE ('{datanasename}',1,{PageID},3)
GO

provided me with the data on that page.

This allowed me to output all of the tail end data after the corrupted pages into a text file. The data output was in the format similar to :
Slot 54 Offset 0x10d4
---------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
484730D4: 003e0030 000a5d59 00000001 00000000 0.>.Y]..........
484730E4: 00000000 00000000 00000000 00000000 ................
484730F4: 0000002b 00000000 00000000 00000000 +...............
48473104: 00000000 00000000 00000000 00120001 ................
48473114: 02000000 4e004c00 494c4300 5453 .....L.N.CLIST
LegId = 679257
PassType = CLI
SpaceType = ST
NumSpacesPu = 1
NumSpacesDo = 0
FareTypeId = 0
FareAmount = 0
FundingSource1Amount = 0
FundingSource2Amount = 0
FundingSource3Amount = 0
FundingSourceId1 = 43
FundingSourceId2 = 0
FundingSourceId3 = 0
FundingSource4Amount = 0
FundingSource5Amount = 0
FundingSourceId4 = 0
FundingSourceId5 = 0
FareCalcType = 1

I ran the DBCC PAGE for the newer pages/data that did not appear on the restored database , which I had restored to a copy table. I loaded the resulting (and large) text file back into a temporary table, cleaned up the data to leave the column names and values and then transposed these back into columns with a little bit of TSQL scripting.
I was then able to insert the latest data back into the restored table, drop the corrupted one and rename the restored one to replace it.

No data loss, but there was some loss of functionality to the users system during the fix.

It's a horrible position to be in and not an elegant or easy fix, but it can be done this way if you run out of ideas.



Paul Randal
Paul Randal
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15630 Visits: 1734
Ness (1/29/2013)
Hi,

Would you run the DBCC CHECKDB with the REPAIR_REBUILD option (after recovery from a backup/HA solution)? I seem to remember that this was the preferred option the MS 2005 / 2008 MCITP learning. I know that real life can be far detached from the learning, or are you attempting to fix any Ix's individually?



I'd only run REPAIR_REBUILD if they were just nonclustered index errors that I couldn't fix by doing a manual rebuild or a drop/create inside a transaction. The advice in the MCITP learning is incorrect. Never put the database offline (i.e. from going into SINGLE_USER) if you can avoid it.

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
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15630 Visits: 1734
SALIM ALI (1/29/2013)
Failover, Restore and Repair
I think one has been forgotten, and it's one that's not to be taken likely because you have to know your data. This method is Reconstruction.


Nope - not forgotten, just not useful to the vast majority of DBAs out there so not worth mentioning in a short, 1-200-level article such as this one.

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
@SQLFRNDZ
@SQLFRNDZ
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4413 Visits: 1228
Hi Paul,

I have similar issue yesterday occurred after upgrading to the new version sql2008r2 from 2005 version. We haven't changed the compitible mode after the upgarde.

I have followed all steps in your blogpost


Initially, I have tried regular recover option like below

ALTER DATABASE [MYDB] SET EMERGENCY;
GO
ALTER DATABASE [MYDB] SET SINGLE_USER;
GO
DBCC CHECKDB (N'MYDB', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO


But received Error:

The database 'MYDB ' is in 611. The database needs to be updated to the latest version.

Then I followed your blog post below:

http://www.sqlskills.com/blogs/paul/creating-detaching-re-attaching-and-fixing-a-suspect-database/


As it is reporting database, we are not concerned about the data but need the reporting store procedure from the corrupted dB.

Issue: DBCC checkdb cannot be run , also I checked other post of you on fatal errors where dbcc checkdb can not be run, In this case Is there anyway we could get the stored procedures out of the corrupted database.


--SQLFRNDZ

Paul Randal
Paul Randal
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15630 Visits: 1734
Nope - something stopped the upgrade from completing. You'll need to go back to the copy you still have on 2005 and figure out what the corruption is there.

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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)SSC Guru (430K reputation)

Group: General Forum Members
Points: 430116 Visits: 43454
I'm one of "those" people that kept saying that there were a million articles on how to determine if corruption had occurred and very few on what to do about it. Thanks for rising to the occasion, Paul, and well done!

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
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