asked
sql 2000 DB Suspect\offline
Hi, 3 of my SQL 2000 databases are in suspect\\offline.How to bring them online? And also can you please tell what are the reasons for a DB going to suspect\\offline. Thanks
For the database to go suspect or offline, an event occurred, probably something with hardware. When SQL Server brings the databases online, it performs a simple validation check. if they failed, they're marked as suspect. Basically you have potentially corrupt databases. The first thing you need to do is run DBCC CHECKDB('databasename') for each of your databases that is suspect. Based on the information that returns, you can try to figure out what to do. Do you have backups in place?
Gail Shaw has an excellent article on SSC, which is titled [Help, my database is corrupt. Now what?][1] This is a very good read. [1]:
http://www.sqlservercentral.com/articles/Corruption/65804/
Why can't you run DBCC CHECKDB? Without that, it's impossible to know what's going on. You might have something very easy to fix, but there's no way to know what's broken otherwise. In answer to your question, if they've been offline for months, then go ahead and drop them. They can't be used when they're offline.
Thanks GrantFritchey and Oleg, where can i get the information about this,when did they went suspect\offline,because i am expecting these DB's went into Suspect\offline mode long back (may be more than 3-4 months). And i am not able to run DBCC CHECKDB('DBNAME'). what are the other options that i can do?
There are many, many reasons for a database to become marked "suspect", and it's not always because the database has become inconsistent. If you restart the SQL Server service, SQL Server will try to set the database online, and if it fails, it will mark it as suspect. If that happens, you can have a look in the ERRORLOG why SQL Server didn't manage to set it online. Databases having Auto Close set to 1 is one cause for randomly getting suspect databases. If a process accesses the database file while SQL Server is trying to set it online, it will be marked as suspect by SQL Server.
Ther may be many reasons for db in suspect/offline -->datafile or logfile may be inaccessible due to virus scan(generally we do) at the time of databases are staring up -->curruption in the data or log files. -->if the restor of large databases or in the middle and some one intentionally stop(kill) the process the process may be in hang state and the db status may show suspect. (some times we do resore using 3rd party tools. due to network issue cummunication may fail between server and the tool(tool may be in another server)) -->some one intentionally bring the DB offline Solution: As Magnus said check in the errorlog you may get some information related to why not the server bring the db online, run the dbcc checkdb you will get some what information. (you said that you are unable to run dbcc than what the error you are getting while running it),If you unable to run dbcc than try to backup the tail log (if log file is acessible) and resore with recent backups.
@srivivek If you still have ERRORLOG-files from when you think they were first marked suspect, I think they would reveal that information.
