There can be a possibility that when you connect to an SQL Server Instance you will find the database being marked as SUSPECT. During such scenarios, you will not be able to connect to the database. In this article we will go through the steps which you need to follow to recovery a database which is marked as SUSPECT. The steps mentioned in this article works on SQL Server 2005 and higher versions.
Some of the reasons why an SQL Server database can be marked as SUSPECT
1. Database could have been corrupted.
2. There is not enough space available for the SQL Server to recover the
database during startup.
3. Database cannot be opened due to inaccessible files or insufficient memory or
disk space.
4. Database files are being held by operating system, third party backup
software etc.
5. Unexpected SQL Server Shutdown, Power failure or a Hardware failure.
Due to a hardware failure one of our database namely BPO was marked SUSPECT when the SQL Server came back online. Already due to the hardware failure we had downtime for more than two hours and adding to that when the server came back online our mostly critical database was marked as SUSPECT.
Steps to Recover Database Marked as SUSPECT
1. Execute the below mentioned TSQL code to identify all the databases which are marked as SUSPECT.
USE master
GO
SELECT NAME,STATE_DESC FROM SYS.DATABASES
WHERE STATE_DESC='SUSPECT'
GO
2. Open the latest SQL Server Error Log and check for errors logged for the database which is marked as suspect. You can open SQL Server Error Log by expanding Management Node -> SQL Server Error Logs. In my server I could find below mentioned entries in SQL Server Error Logs.
3. When a database is in SUSPECT mode you will not be able to get connected to the database. Hence you need to bring the database first in EMERGENCY mode to repair the database. Execute the below mentioned TSQL code to bring the database in EMERGENCY mode.
USE master
GO
ALTER DATABASE DBNAME SET EMERGENCY
GO
Once the database is in EMERGENCY mode you will be able to query the database.
4. Execute the DBCC CHECKDB command which will check the logical and physical
integrity of all the objects within the specified database.
DBCC CHECKDB (DBNAME)
GO
5. Next step will be to bring the user database in SINGLE_USER
mode by executing the below mentioned TSQL code.
ALTER DATABASE DBNAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
6. Once the database is in SINGLE_USER mode execute the
below TSQL code to repair the database. When you repair
your database using REPAIR_ALLOW_DATA_LOSS option of
DBCC CHECKDB command there can be some loss of data.
Once the database is successfully repaired using
REPAIR_ALLOW_DATA_LOSS option of DBCC CHECKDB command
then there is no way to go back to the previous state.
DBCC CHECKDB (DBNAME, REPAIR_ALLOW_DATA_LOSS)
GO
7. Finally, execute the below mentioned TSQL command to allow MULTI_USER access to the database.
ALTER DATABASE DBNAME SET MULTI_USER
GO
Note:DBNAME Is Your Actual Suspected database name
