DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to recover a database marked suspect?

A SQL Server database can become marked suspect for several reasons, therefore regular database backup is mandatory. 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.

-- Check suspect state (status) of all databases

SELECT DBName=NAME, [Status]=state_desc

FROM master.sys.databases

WHERE state_desc='SUSPECT'
------------                      

-- 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 serve

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.

Related articles:

Missing device causes database to be marked suspect

FIX: A database is marked suspect when you open the database in SQL Server 2000 View products

Recommended actions for corrupt or suspect databases

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE