Q2.3: How do I turn off marked suspect on my database?

Say one of your database is marked suspect as the SQL Server is coming up. Here are the steps to take to unset the flag.
Remember to fix the problem that caused the database to be marked suspect after switching the flag.

Pre System 10

  1. sp_configure "allow updates", 1
  2. reconfigure with override
  3. select status - 320 from sysdatabases where dbid = db_id("my_hosed_db") - save this value.
  4. begin transaction
  5. update sysdatabases set status = -32767 where dbid = db_id("my_hosed_db")
  6. commit transaction
  7. you should be able to access the database for it to be cleared out. If not:
    1. shutdown
    2. startserver -f RUN_*
  8. fix the problem that caused the database to be marked suspect
  9. begin transaction
  10. update sysdatabases set status = saved_value where dbid = db_id("my_hosed_db")
  11. commit transaction
  12. sp_configure "allow updates", 0
  13. reconfigure

System 10

  1. sp_configure "allow updates", 1
  2. reconfigure with override
  3. select status - 320 from sysdatabases where dbid = db_id("my_hosed_db") - save this value.
  4. begin transaction
  5. update sysdatabases set status = -32768 where dbid = db_id("my_hosed_db")
  6. commit transaction
  7. shutdown
  8. startserver -f RUN_*
  9. fix the problem that caused the database to be marked suspect
  10. begin transaction
  11. update sysdatabases set status = saved_value where dbid = db_id("my_hosed_db")
  12. commit transaction
  13. sp_configure "allow updates", 0
  14. reconfigure
  15. shutdown
  16. startserver -f RUN_*