August 29, 2002 at 8:19 am
font=Verdana][/font=Verdana]
Hi,
We have an ongoing problem with our Win2K server - it periodically freezes and there is no way to 'thaw' it except by powering the system off. Normally this occurs out of business hours so there isn't a problem, but today I have ended up with a torn page in one of my SQL2000 databases and so I need to repair it.
I think that I can repair the database using DBCC CheckDB but the database is currently offline (the node in enterprise manager is marked as Suspect\Offline). I have tried every method I can think of to bring it online (EM, sp_dboption, ALTER DATABASE) but everything I try just stops the SQL 2000 service.
Interestingly, if I try to use DATABASEPROPERTYEX to test any status of any DB on the system it always returns NULL, but I don't know if this is significant or not. I do have a bad feeling about it though ;-).
If anybody has any ideas at all I'd be grateful to hear them.
Cheers,
Bob.
August 29, 2002 at 8:44 am
Try running sp_resetstatus [ @DBName = ] 'database' to reset the suspect flag then try repair.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
August 29, 2002 at 9:01 am
Thanks, I tried that and it seems to reset the flag, but the service still shuts down if I try to bring the database on-line. If I try to repair using DBCC CheckDB without bringing the database online it tells me that the database cannot be opened because it's offline :-(.
I read somewhere that using sp_ResetStatus requires a reboot before you try to access the database, do you know if that's true? Books online seems to imply that stopping and starting the service will do, but it doesn't seem to have any effect.
September 3, 2002 at 10:45 am
Do you have the db_option for torn page detection set to on? If so, it may be shutting down(marking suspect) the database as soon as it finds a torn page-preventing you from running check db. Run sp_resetstatus and change the db_option for torn page detection to off. Then try running checkdb.
September 4, 2002 at 12:59 am
Thanks, I tried this too. The problem is that I can't run DBCC CheckDB on an offline database, but when I try to bring the DB on line the service stops.
September 4, 2002 at 4:02 am
Ok this is round about but try and see wht happens. SHut down the service, copy the db files (log(s) and data(s)) to a test server, then run sp_attach_db (note: if one log file it could be corruption in the log so you may try with sp_attach_single_file_db to get the database up). If the database comes online there check it for errors with DBCC CheckDB. If all is fine then, shut down test, if you restarted prod the shut down SQL server service, rename the old db files for backup copy, copy the ones moved to test back to prod, try again to bring online (you may need to use detach and attach method to get back if paths were deifferent).
If this fails and same thing occurrs on test or DB will no come back up then you will need to retreive your last backup. The database has to be corrupted beyond restore.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
September 5, 2002 at 9:55 am
Another thing to try (and this is not documented and is at your own risk, but I have used it a few times to get a seemingly unrecoverable db back in business), is to put the database into emergency mode. Set the status field in sysdatabases to 32768 for the row for your bad db. Once you do this, you should be able to run checkdb and deallocate the page that is bad. The db will be in single-user mode, so you will have to change that before allowing anyone else back in.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply