DB marked as suspect...can not alter table
-
My SQL 2000 SP3 server has a DB marked as suspect. I tried to take offline in hopes of bringing it back online. I looked in the log and saw it actually recoverd 100%, then a recovery checkpointing occured and then I got an I/O error error 33(the process can not access the the file because another process has locked a portionof the file) detected during write at offset 0x0000afab6000 in file db.mdf"
The next two lines show that there was an error 823, severity 24, state 3, and the db could not recover.
I was wondering if I could run "sp_resetstatus" on the db and that would set it right, given it actually recovered, or so the log file says. OR should i remove the data and log file and then restore those 2 files form tape and then start the SQL service? The DB is 40 GB.
Any and all help is appreciated.
-
Are there multiple files for the data? You can use sp_resetstatus, but you want to resolve the issue that caused it to be suspect. Perhaps a file was locked by some other process?
It's a fairly safe process, but I'd make a copy of the mdf/ldf just in case.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
Thanks for the prompt reply. I have only 2 files, the mdf and the ldf. I just did the sp_resetstatus db and it said that before I access it I must recover the db(makes sense). I then reboot the server and the Ent manager shows it as suspect again and by looking at the logs, it shows that it was recovered 100% and then it fails after that with an I/O error (same errors). Any ideas on what could be locking the file? I am so confused...
Any other suggestions?
~PJ
-
Hi Pool, even though you used to sp_resetstatus, before that you have to update the status column. I too faced this problem and search in BOL that helped me to recover the suspected database. BOL gives u the clear status.
When u have bol why do u worry man.
ganpathy
-
Do you do disk backup on your shop ? Is the disk backup (or network backup). somehow hung casue you the problem ?
-
I think you may have some other isssue. If you change the status and reset the suspect flag and it still doesn't recover, you probably need to call MS. I'd be sure that some backup process (like Backup Exec or Veritas) isn't hung and holding the file open as well.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply