My SQL Server database has been marked "suspect" - what can I do?

A. In addition to these ideas, also check out for the Microsoft Knowledgebase. Specifically Q165918.

Firstly look in <sql>\LOG and look at all recent errorlog(s). There WILL be an indication here as to why the database has been marked suspect. You need to fix whatever the problem is first (i.e. missing file, permissions problem, hardware error etc.)

Then, when the problem has been fixed and you're either sure that the data is going to be ok, or you have no backup anyway, so you've nothing to lose, then change the database status to normal and restart SQL Server. To change the database status, and to get more information on recovery, look up the sp_resetstatus sp in the Books Online.

If you don't have access to sp_resetstatus information, then the short version of this is :-

UPDATE master..sysdatabases SET status = status ^ 256 WHERE name = <dbname>

If the database still goes back into suspect mode, and you can't fix the original problem, and you have no recent backup, then you can get information out of the database by putting it into emergency mode. If you do this, extract the data/objects out with bcp/transfer manager and then rebuild the database. Note that the data may be corrupt or transactionally inconsistent.

Issue the following command to put the database into emergency mode (you'll need to allow updates first)

UPDATE master..sysdatabases SET status=-32768 WHERE name='<dbname>'

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.