database(suspect) in sql server 2000

Database in suspect mode means that database is unavailable for use.
Reasons:
•      MDF and LDF files of database are unavailable during startup.
•      LDF file are missing or corrupted.
•      MDF file page allocation problems.
•      Database resource can be denied by the operating system.
1. The first one can be resolved by executing "sp_resetstatus" storedprocedure and restart SQL server services
exec sp_resetstatus 'db_name'
Result: (Prior to updating sysdatabases entry for database 'northwind', mode = 0 and status = 1073741840 (status suspect_bit = 0).No row in sysdatabases was updated because mode and status are already correctly reset. No error and no changes made.)
2. For Second one..Start the database in Emergency mode , Update the Status column in master..sysdatabases table for that database to 327268.
   After this database will be usable with out transaction log. AFter this create a new database and use DTS to transfer objects and data.
3. Third one is critical , try executing DBCC CHECKDB with REPAIR_REBUILD option. If the problem is not rectified with restore from Backup.

Steps if the Database .LDF file is corrupt:

1.      Database showing in Suspect condition. In Suspect mode value of status column in Sysdatabase table (Master DB) get changed from value “16 (Default)” to some other value (Eg: 234587).
2.      Kill all the connections on the database.
3.      Put the database in Single User Mode.
4.      Take the database to emergency mode.

Taking DB in Emergency Mode:
      For putting the database in Emergency Mode, change the value of status column in Sysdatabase table in Master DB to ‘327286’.

If while placing the database in Emergency Mode gives you an ODBC error (cannot make the changes)……  Go to Server Properties - Server Setting Tab - Click on Checkbox… “Allow Modifications to be made directly to the system catalog”.

Then again change the value of status column in Sysdatabase table in Master DB to ‘327286’.
By changing the value db will be in Emergency mode. Putting db in Emergency mode means that upto the point the .MDF  is not corrupted, we can export (DTS)the data.


5.      Sometimes while detaching the database,  the detach database option will be hidden. Try to again change the value to ‘16’ and then Detach the database.
6.      Again while Attaching the database we get an option to re-create the log file. Reattach the db.

Steps if the Database .LDF file is Missing:

Use the command sp_attach_single_file_db, this
command will work if the database was cleanly shutdown.
If it doesn't work what you can do is trick SQL Server to believe the
database is attached. How do you do that?
1. Create a blank database with the same name, and the same file structure
(location and names).
2. Stop SQL Server
3. Replace the blank mdf file with the original mdf file you have
4. Restart SQL Server
At this point the database will be marked suspect, as the log does not go
in accordance with the mdf file. We need to place it in Emergency Mode and
either DTS the contents out or rebuild the log. I am going to ellaborate on
the first option
1. Place database in single user mode
   sp_configure 'allow', 1
   reconfigure with override
   go
   update sysdatabases set status = 32768 where name = '<dbname>'
   go
2. Restart SQL Server
3. The database is in Emergency Mode and you can DTS its contents out
4. Remember to turn 'allow updates' off
   sp_configure 'allow', 0
   reconfigure with override
   go



Any help give a shout...
Gaurav Anand