How To Repair A Suspect Database In Microsoft SQL

Here is a Microsoft SQL tip. A fellow team member recently dealt with this issue. The recommended steps come from the support articles of Managed.com.

Symptom:

In Microsoft SQL Server, you have a database 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.

Resolution:

These steps require you to have Microsoft SQL Server Management Studio installed on your computer. Once this is done, proceed as follows.

1. Open Microsoft SQL Server Management Studio and connect to your database. 

2. Click the New Query button.

 

3. 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

4. Click Execute. Your database should no longer be tagged as "suspect" and you should be able to access it.

To learn more, see these articles:

• How To Repair A Suspect Database In MSSQL
• How To Connect to Your MS SQL 2012 Database and Disable Collation

-