Category Archives: Recovering SQL Database from SUSPECT Mode

How to REPAIR SQL database in suspect mode


Sometimes when you connect to your database server, you may find it in suspect mode. Your database server won’t allow you to perform any operation on that database until the database is repaired.

A database can go in suspect mode for many reasons like improper shutdown
of the database server, corruption of the database files etc.

To get the exact reason of a database going into suspect mode can be found
using the following query.

 

DBCC CHECKDB (‘DBName’)
WITH NO_INFOMSGS,
ALL_ERRORMSGS

  
/*
Output of the above query will give the errors in the database.*/
/*To repair the database,
run the following queries in Query Analyzer*/
 

 

EXEC sp_resetstatus ‘DBname’;

ALTER DATABASE DBname SET EMERGENCY

DBCC checkdb(‘DBname’)

ALTER DATABASE DBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB (‘DBname’, REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE DBname

SET MULTI_USER

 

 

_______________________________________________________________________________

Recover SQL Server Database from SUSPECT


 
An SQL 2005 database can become marked suspect for several reasons. Possible causes include:
1. Denial of access to a database resource by the operating system

2. Anti-virus/3rd party software blocking access

3. The hardware/network issue or corruption of database file(s)

  • Prior to resetting the status, the database file issue should be resolved.
    First check error logs and Event Viewer for related messages.
    If logs offer no help, then one way of resolution: create an empty database, copy over the damaged
    database files and restart the server.
    The (copied over) database may come up in the suspect mode.

 

  • The recovery methods below are last resort only.
    At minimum the server should be rebooted (Windows restart – hardboot), if possible at all,
    a database copy should be created. If backup files exist, point-in-time recovery is preferred to emergency repair.

 

The following script resets the status of the database and checks the database for integrity.
— SQL Server 2000/2005/2008

— Recover database suspect sql server
USE master;

GO

 

EXEC sp_resetstatus ‘CopyOfAdventureWorks2008’;

GO

USE CopyOfAdventureWorks2008;

 

DBCC CHECKDB WITH NO_INFOMSGS;

GO

The latest database marked suspect recovery process:

— SQL Server 2005/2008

— Recover database marked suspect sql server
USE master;

GO

ALTER DATABASE CopyOfAdventureWorks2008 SET EMERGENCY

GO

ALTER DATABASE CopyOfAdventureWorks2008 SET SINGLE_USER

GO

DBCC CHECKDB (CopyOfAdventureWorks2008, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;

GO

 

USE CopyOfAdventureWorks2008;

 

DBCC CHECKDB WITH NO_INFOMSGS;

GO
In both cases save as much data as possible into a clean database.
Perform troubleshooting to locate the problem source.

********************************************************************************************************

%d bloggers like this: