SQL Database Suspect Issues and Solution

Some time databases may be suspect due to insufficient space or HDD issues in SQL 2005/2008. we can recover those suspected databases from SQL 2005/2008 using below queries.

Execute Below queries one by one using Query Analyzer

Step 1

Stop and restart the instance of SQL Server using 
EXEC sp_resetstatus 'myDB';

Step 2 

EMERGENCY mode is not SINGLE_USER mode. A database needs to be in SINGLE_USER mode for repair to run and EMERGENCY mode allows multiple connections from members of the sysadmin role. 
ALTER DATABASE myDB SET EMERGENCY

Step 3

Note that the usual error log entry from running DBCC CHECKDB is preceded by ‘EMERGENCY MODE‘ this time.
DBCC checkdb('myDB')

Step 4

Alter Database with single user using below query
ALTER DATABASE myDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Step 5

Repair Database using

 DBCC CheckDB ('myDB', REPAIR_ALLOW_DATA_LOSS)

Step 6

 Alter database for Multiuser

ALTER DATABASE myDB SET MULTI_USER
Change myDB as your Suspected database Name.

Recover Query for Pincode Database Suspected : Example

EXEC sp_resetstatus 'pincode';
ALTER DATABASE pincode SET EMERGENCY
DBCC checkdb('pincode')
ALTER DATABASE pincode SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('pincode', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE pincode SET MULTI_USER