Database Corruption problems

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Data Corruption » Database Corruption problems

18 posts, Page 2 of 2

««1 2

Database Corruption problems

Rate Topic

Display Mode

Topic Options

Author

Message

Paul Randal

Posted Friday, June 27, 2008 12:50 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:23 PM
Points: 1,905, Visits: 1,601

PSS won't be able to do anything different.

One of the steps of emergency mode repair is to try forcing recovery with the equivalent of WITH CONTINUE_AFTER_ERROR - looks like in this case it's caused the recovery code to hang. This is only the second time I've seen it fail since I wrote it - but it's a last ditch effort when everything's corrupt and there's no backup so I'm not surprised there are some cases where even it can't 'fix' things.

Your only options at this point are:
1 EMERGENCY mode and extract into a new database
2 delete the log file and run repair

#2 should only be attempted if you're comfortable. It's really ok as the log and database are corrupt anyway, so you're not losing anything by manually throwing away the transaction log. Follow these steps:
- shutdown
- make a complete copy of all database files on separate storage (in case you want to go back to your current state)
- delete the log file from the database
- start up
- put the database in emergency mode
- run repair - this won't try to run recovery this time, but will simply create a new log file and then run a full repair

Your data will be transactionally inconsistent and you'll have lost some but the database will be usable by SQL Server (not necessarily your application).

Do all of this at your own risk. The most important thing is to make a copy of all the database files before you start so you can go back to your current state.

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 #525287

Quinn-793124

Posted Friday, June 27, 2008 1:26 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, February 22, 2013 1:36 PM
Points: 110, Visits: 429

Thanks Paul.

I had read about deleting the log file on another site. I just didn't have the guts to try it. Maybe I will give it a whirl. At this point I have nothing to lose.

Thanks

Quinn

Post #525307

Malcolm Daughtree

Posted Wednesday, March 03, 2010 4:34 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 02, 2011 9:50 PM
Points: 189, Visits: 335

Hey Paul, If you won't beat your own drum I will

GO HERE !!!!

http://www.microsoft.com/emea/teched2008/itpro/tv/default.aspx?vid=78.

LEARN

Enjoy!

I did and have discovered the Power of DBCC page and Checkdb, and directly aided me recovering a large Database faster than a 4 hour restore.

Also I'd suggest you hunt up a copy of Rodney Landrum's eBook "SQL Server Tacklebox" especially the last chapter.

CodeOn
Tongue

Post #876441

SQLQuest29

Posted Thursday, March 04, 2010 12:48 PM

Say Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 9:48 AM
Points: 713, Visits: 2,842

Malcolm Daughtree (3/3/2010)

Thanks Malcolm for pointing to a wonderful webcast of Paul explaining details of corruption with practical demos. I saw the whole and learned a lot. Smile

Sometimes just reading forums regularly helps to gain knowledge !

\\K

HTH,
Cheers !

"Never take life too seriously, nobody gets out of it anyways ! BigGrin

When your love and skills unite, expect a masterpiece !"

Post #877106

TheSQLGuru

Posted Thursday, March 04, 2010 1:32 PM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 8:50 AM
Points: 3,572, Visits: 5,106

Sometimes just reading forums regularly helps to gain knowledge !

Actually you didn't need to read this forum to gain knowledge. That stuff you watched has been on the web for a long time now. BigGrin

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail

Post #877138

SQLQuest29

Posted Thursday, March 04, 2010 6:02 PM

Say Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 9:48 AM
Points: 713, Visits: 2,842

TheSQLGuru (3/4/2010)

Sometimes just reading forums regularly helps to gain knowledge !

Actually you didn't need to read this forum to gain knowledge. That stuff you watched has been on the web for a long time now. BigGrin

I agree .. but sometimes the obvious remains obscure ! Smile

\\K

HTH,
Cheers !

"Never take life too seriously, nobody gets out of it anyways ! BigGrin

When your love and skills unite, expect a masterpiece !"

Post #877312

Malcolm Daughtree

Posted Thursday, March 04, 2010 6:06 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 02, 2011 9:50 PM
Points: 189, Visits: 335

And sometimes the sign posts get lost..... all we can do is but try ...

Tongue

Post #877317

stephencook2913

Posted Saturday, April 14, 2012 5:01 AM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, April 14, 2012 4:59 AM
Points: 2, Visits: 6

Server reboot is not an easy job as it looks like. You should be very cautious while rebooting your SQL server because any minor mistake can corrupt your whole SQL database, i think the same happened with you and you had a list of corrupted and suspected files which you tried to fix with some coding but the problem is not resolved because these coding don't provide a proper solution. You need to have a commercial tool that can fix the errors of your corrupted SQL database.

You can get more information about the management tool from http://www.mssqlrepair.n.nu/repair-corrupt-ms-sql-database.html, it will help you to repair the corrupted files.

And one more thing I would like suggest you that please don't reboot your MS SQL Server incautiously next time.

Post #1283618

« Prev Topic | Next Topic »

18 posts, Page 2 of 2

««1 2

Permissions