Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

My database in sql server 2008 has marked suspect.

How can I fix this problem?

share|improve this question
    
Read this article before you try 'fixing': sqlservercentral.com/articles/Corruption/65804 –  DaniSQL Apr 10 '12 at 20:01
add comment

migrated from stackoverflow.com Apr 9 '12 at 7:48

This question came from our site for professional and enthusiast programmers.

3 Answers

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.

share|improve this answer
    
+1 for "DO NOT use DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS parameter." Seriosuly - that particular command is your last attempt at recovery when all else fails and you have no backups. That switch REPAIR_ALLOW_DATA_LOSS actually means I_WILL_TRASH_95%_OF_YOUR_DATA and YOU_HAVE_NO_RIGHT_TO_COMPLAIN. TO lighten your mood (since suspect databases are horrible) here is Pop Rivett's semi-serious article on database corruption at Simple-Talk. –  Greenstone Walker Apr 2 '13 at 22:46
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.