How To Repair A SQL Server Database Marked As SUSPECT

How To Repair A SQL Server Database Marked As SUSPECT

If your Database unexpectedly "Database_Name" is marked as Suspected, then follow the below steps to bring the database to normal stage. 

Step 1.

EXEC sp_resetstatus 'AdventureWorks'

GO 

Step 2. Push the database to emergency mode from suspect.

ALTER DATABASE AdventureWorks SET EMERGENCY

GO 

Step 3. For checking the database errors.

DBCC checkdb('AdventureWorks')

GO 

Step 4. Push the database to a single user mode.

ALTER DATABASE AdventureWorks

SET SINGLE_USER

WITH ROLLBACK IMMEDIATE

GO 

Error Correction

DBCC CheckDB ('AdventureWorks',

REPAIR_ALLOW_DATA_LOSS)

GO 

Bring the database to Multi-user mode.

ALTER DATABASE AdventureWorks SET MULTI_USER

GO 

Rebuild the index 

Here is the complete script for rebuilding all the indexes in the database.

DECLARE @TableName VARCHAR(300)

DECLARE @sql NVARCHAR(550)

DECLARE @fillfactor INT

SET @fillfactor = 80

DECLARE TableCursor CURSOR FOR

SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name

AS TableName

FROM sys.tables

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sql = 'ALTER INDEX ALL ON ' + @TableName +

'REBUILD WITH (FILLFACTOR = ' +

CONVERT(VARCHAR(3),@fillfactor) + ')'

EXEC (@sql)

FETCH NEXT FROM TableCursor

INTO @TableName

ENDCLOSE TableCursor

DEALLOCATE TableCursor

GO 

Finally Verify database status by running below query.

SELECT [Name], DBID, Status

FROM master.dbo.sysdatabases

GO