Tuesday, February 14, 2012

Database corrupted?

Hi all,

Had a disk problem here, and all apps connected to our database hung. Nothing was responding, rebooted, and mssqlserver hangs on startup.

If i move the mdf and ldf files of the database in question to another folder, mssqlserver starts up ok, with the db marked as suspect.

It would seem the the mdf and ldf are corrupted.

I am restoring a backup from yesterday, but this will take hours.

Any suggestions trying to recover it as it sits?

Thanks in advance for any ideas.I had the same problem just last week. The only fix was to restore from last good backup and bring the db forward as far as you can with transaction logs.|||Odds are near certain that you aren't hung, it is just doing a truly massive recovery operation when the server restarts. The only answer I know is to let it recover, then you can go merrily on your way with your data intact.

-PatP|||Let it recover, it that complete, and run a dbcc checkdb to verify.|||Odds are near certain that you aren't hung, it is just doing a truly massive recovery operation when the server restarts. The only answer I know is to let it recover, then you can go merrily on your way with your data intact.

-PatP

The restore completed....now its time for the post-mortem.

The problem I was having was that I could not access the database using Query Analyzer, any application software, or Enterprise Manager. Any attempt to connect would result in a time-out, or just hang when trying to connect.

If the DB is not responding, how can one determine if a recovery operation is in progress?|||I use sp_who. Check for a low numbered (usually 10) spid doing a recovery in the database in question.

-PatP|||In answer to your next question "How do I run sp_who when I can't connect through Query Analyzer?", the answer is to try the OSQL command line utility, which sometimes works when all others fail.|||Good anticipation blindman :)

Thanks for the suggestions. I will file this information away for future reference.

No comments:

Post a Comment