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.

To recover the database from suspect mode executes the following query.

EXEC sp_resetstatus ‘yourDBname’;
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb(’yourDBname’)
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (’yourDBname’, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER

Then getting

Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors

How to resolve this?

share|improve this question

migrated from stackoverflow.com Mar 8 '12 at 8:56

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

3 Answers 3

According to Paul Randal, this seems to be a bug in checkdb. Check this article from Paul which discusses about this issue.

The workaround is to run CHECKDB on the database with TABLOCK

share|improve this answer

Run DBCC CHECKDB without the repair option. What does it tell you? If the system tables are corrupt then it's game over and time to restore from backup.

share|improve this answer

You have a database in MS SQL that is tagged as (suspect) and you are unable to connect to the database.

Possible Causes

  • The database could have become corrupted.

    There is not enough space available for the SQL Server to recover the database during startup.

    The database cannot be opened due to inaccessible files or insufficient memory or disk space.

    The database files are being held by operating system, third party backup software etc.

    There was an unexpected SQL Server Shutdown, power failure or a hardware failure.

Solution

Open Microsoft SQL Server Management Studio and connect to your database

Click the New Query button

Paste the following SQL script into your New Query page replacing [YourDatabase] with the name of your database.

EXEC sp_resetstatus [YourDatabase];
ALTER DATABASE [YourDatabase] SET EMERGENCY
DBCC checkdb([YourDatabase])
ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ([YourDatabase], REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE [YourDatabase] SET MULTI_USER
Click Execute

Source : https://support.appzone.com/kb/a398/how-to-repair-a-suspect-database-in-mssql.aspx

share|improve this answer

Your Answer

 
discard

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