DB marked as suspect...can not alter table

Question

DB marked as suspect...can not alter table

Patrick Jones

Grasshopper

Points: 24
More actions
June 1, 2004 at 9:20 am

#85809

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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736105 More actions · Answer 1

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.

Patrick Jones Grasshopper Points: 24 More actions · Answer 2

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

aiyerganapas Mr or Mrs. 500 Points: 582 More actions · Answer 3

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

John Chiu SSCrazy Points: 2619 More actions · Answer 4

Do you do disk backup on your shop ? Is the disk backup (or network backup). somehow hung casue you the problem ?

Steve Jones - SSC Editor SSC Guru Points: 736105 More actions · Answer 5

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.