Monday, September 6, 2010

How To Solve A SQL Server Database Marked As Suspect Mode

I'm going to keep this short and to the point. You may log into SQL Server some day and find that some of your databases have an exclamation point along with being marked with the following: DatabaseName (Suspect). That just makes you frown. Well I'm going to show you how to turn your frown upside down!

The solution is listed below.

Smooches,

Kila

Problem:
SQL Server marks your database as (Suspect). It won't open and you don't know what to do.

Solution:
To solve the problem, run the following in query analyzer.


EXEC sp_resetstatus 'YourDatabaseName';

ALTER DATABASE YourDatabaseName SET EMERGENCY

DBCC checkdb('YourDatabaseName')

ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE YourDatabaseName SET MULTI_USER

After you run this, right click on the database and hit Refresh. There you go! Now your frown is turned upside down!