Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Corruption problems


Database Corruption problems

Author
Message
Paul Randal
Paul Randal
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2161 Visits: 1714
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
Quinn-793124
Quinn-793124
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 463
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
Malcolm Daughtree
Malcolm Daughtree
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
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
:-P
SQLQuest29
SQLQuest29
Right there with Babe
Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)

Group: General Forum Members
Points: 746 Visits: 4311
Malcolm Daughtree (3/3/2010)
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
:-P





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. :-)

Sometimes just reading forums regularly helps to gain knowledge !

\\K

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor :-)
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5964 Visits: 8313
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. :-D

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
SQLQuest29
SQLQuest29
Right there with Babe
Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)

Group: General Forum Members
Points: 746 Visits: 4311
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. :-D


I agree .. but sometimes the obvious remains obscure ! :-)

\\K

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor :-)
Malcolm Daughtree
Malcolm Daughtree
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 335
And sometimes the sign posts get lost..... all we can do is but try ...

:-P
stephencook2913
stephencook2913
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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.


GabrielPoulsen
GabrielPoulsen
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 7
If you need to manually restore an SQL Database in Management Studio you can follow the instructions below for the version of SQL Server running on your server.
SQL 2012:
1. Log onto SQL Server Management Studio.
2. Navigate to the database you wish to restore.
3. Right Click > Tasks > Restore > Database.
4. Once on the General tab set the source to Device > Select Backup Device > Add > Navigate to the .bak file you're restoring to.
5. Ensure the Destination Database is the correct one.
6. Select the Options tab.
7. Check "Overwrite the existing database (WITH REPLACE)" and "Close existing connections to destination database".
8. Uncheck "Take tail-log backup before restore".
9. Click OK.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47263 Visits: 44392
6 year old thread, and a completely irrelevant reply to boot.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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


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