How to fix a suspect database with SQL Management Studio

Introduction

This topic explains the process to recover a database which is marked as SUSPECT.

• This process works on SQL Server 2005 and higher versions.

Some of the reasons why an SQL Server database can be marked as Suspect are:

• Database could have been corrupted

• There is not enough space available

• Database cannot be opened due to inaccessible files or insufficient memory or disk space

• Database files are being affected by operating system, third party software

• Unexpected SQL Server Shutdown, Power failure or a Hardware failure

Steps to Recover a Database Marked as Suspect

When a database is in Suspect mode connection to the database will not be possible. The first step is to get the database in Emergency mode to repair the database.

1. Execute the following TSQL code to get the database in Emergency mode:

Use master

GO

ALTER DATABASE BPO SET EMERGENCY

GO

Once the database is in Emergency mode you will be able to query the database:

2. Execute the DBCC CHECKDB command. This will check the logical and physical integrity of all the objects within the specified database.

DBCC CHECKDB (IRIS)

GO

The next step will be to bring the user database in Single_user mode.

3. Execute the following TSQL code to get the database in Single_user mode:

ALTER DATABASE IRIS SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

Once the database is in Single_user mode execute the TSQL code to repair the database. When the database is repaired using REPAIR_ALLOW_DATA_LOSS option of DBCC CHECKDB command, there can be some loss of data.

• Once the database is successfully repaired there is no way to go back to the previous state.

4. Execute the following code to repair the database:

DBCC CHECKDB (BPO, REPAIR_ALLOW_DATA_LOSS)

GO

5. Finally, execute the TSQL command to allow Multi_user access to the database:

ALTER DATABASE BPO SET MULTI_USER

GO