Sql Server 2008 database is suspected

Posted on

Question :

My database in sql server 2008 has marked suspect.

How can I fix this problem?

Answer :

At first

  • DO NOT detach your database
  • DO NOT restart your server or services
  • DO NOT use DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS parameter.

Second, you need to know the cause of suspection. There is the chance that there is no more space on the database disk. In this case you need to free some space and then try to return the database to normal state using sp_resetstatus.

But if database seems to be corrupted, you need to try to fix the database using DBCC CHECKDB with appropriate parameters. But the database needs to be set in EMERGENCY mode first:

ALTER DATABASE [yourdatabase] SET EMERGENCY;

Now, you can execute the DBCC CHECKDB command.
Be careful with result of DBCC execution: if you see the errors on the pages with data, you need to restore your database from backup, because you can lost the integrity of your data; But obviously (in my expirience) DBCC returns the errors on the pages with indexes, and in most cases you can rebuild your indexes.

Finally, you need to fix the cause of the problem. If your problem concerned with disk space, move the data to larger disks. If your database is corrupted because windows is unexpectelly shutted down – move your database to another server, and so on.

I think you need to read this article and the forum discussion below to get some basic information to start the recovery.

Do you have a backup? Suspect database generally means there is database corruption and the best solution is to recover from a backup. That being said here’s a good link that describes your options:

http://deepakrangarajan.blogspot.com/2007/08/how-to-recover-from-suspect-database.html

You need to run DBCC CHECKDB on the database. That will identify what the issues might be that are causing the database to be marked suspect. If it’s something simple, like a non-clustered index with mismatched pages, you can drop & recreate the index. If it’s something like a data table, you’ll probably have to go to your backups and run a restore operation.

Leave a Reply

Your email address will not be published. Required fields are marked *