Just a day before, come across new issue of database. For some reason database found in suspect mode ! And that was horrible moment like Repair SQL Server 2008 R2 Suspected Database. Tried reinstalling SQL server multiple times but no luck.

Just a day before, come across new issue of database. For some reason database found in suspect mode ! And that was horrible moment like Repair SQL Server 2008 R2 Suspected Database

After some search, we got brief idea that, database can go in suspect mode for many reasons like improper shutdown of the database server, corruption of the database files etc.

To find out what exactly gone wrong with database we tried following query

DBCC CHECKDB (‘YOUR_DATABASE_NAME’) WITH NO_INFOMSGS, ALL_ERRORMSGS

(It wasn’t much helpful in my case. I am just posting here in-case it helps the other !)

After a while we run another block of code, to put the database in emergency mode (Ref  : MSDN)

EXEC sp_resetstatus ‘YOUR_DATABASE_NAME’;
ALTER DATABASE YOUR_DATABASE_NAME  SET EMERGENCY
DBCC checkdb(‘YOUR_DATABASE_NAME’)
ALTER DATABASE YOUR_DATABASE_NAME  SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (‘YOUR_DABASE_NAME’, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE YOUR_DATABASE_NAME SET MULTI_USER

Finally, we got the database back online ! Trouble of Repair SQL Server 2008 R2 Suspected Database given us break from programming. Now we are back on the game. Happy coding.

If you liked this article, then please subscribe to our Blog for more updates like this. You can also connect me on Twitter, LinkedIn and GitHub.