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

 

 

_______________________________________________________________________________

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: