Monday 23 November 2015

How to remove SUSPECT mode from SQL Server database?

I restarted the SQL server instances and i found that few database was disabled with suspect mode.
But i solved this problem easily by executing few simple command in editor. You can see all in given figure.



--First of all i reset database by using following command:
EXEC sp_resetstatus 'yourdbname';

--set emergency mode to a database
ALTER DATABASE yourdbnameSET EMERGENCY

--check database once with default action
DBCC checkdb('yourdbname')

--set single user to databse
ALTER DATABASE yourdbnameSET SINGLE_USER WITH ROLLBACK IMMEDIATE

--Check database with repair & data loss action & remove emergency
DBCC CHECKDB (yourdbname, REPAIR_ALLOW_DATA_LO

SS) WITH NO_INFOMSGS;

--take database out of emergency mode
ALTER DATABASE app_philatelyevents SET online

--allow to multiple user access
ALTER DATABASE app_philatelyevents SET MULTI_USER;