Apr
7
2011
SQL Server

Repair SQL Server 2008 R2 Suspected Database

Author An article by Vinay Patel    Comments 18 Comments

Just a day before , come across new issue of database . for some reason database found in suspect mode ! and that was horrible moment.

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 hepls 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 database back online !

Happy Coding .

Tags: , , , , , ,

Related Posts

About the Author: Vinay Patel

18 Comments+ Add Comment

  • BAHRUZ August 8, 2011 at 8:01 AM

    THANK YOU

    Reply
  • Vinay Patel September 1, 2011 at 4:41 PM

    You Welcome Bahruz !

    Thanks
    Vinay

    Reply
  • Prett Sons December 29, 2011 at 5:17 AM

    Hello Vinay,

    Good to see your article on SQL database issues & you have written it very nicely!! I am Adam & I also love to write article on database issues.

    Reply
  • Frances March 15, 2012 at 12:29 AM

    Thank you for sharing this information! I greatly appreciate that you took the time to write this and share.

    I am not a DBA and I really needed help.

    Reply
  • Anonymous May 10, 2012 at 6:08 PM

    This is an excellent post. This is all I needed to fix the issue.
    Thanks a lot.

    Reply
  • Antonio Assuncao May 11, 2012 at 11:17 AM

    Nice tip!

    Thanks…
    (Y)

    Reply
  • Vinay Patel May 11, 2012 at 1:28 PM

    Glad to hear that it helps you all !

    Thanks
    Vinay

    Reply
  • Sabuni Joseph June 7, 2012 at 8:19 AM

    This is amazing thanks men. it fixed ma problem.

    Reply
  • developer June 27, 2012 at 5:23 AM

    Thanx patel

    this fix the problem

    Reply
  • Arshad July 2, 2012 at 8:15 PM

    Thanks Vinay.

    Really it helps me to get my database back which was in suspect mode. I google everywhere that helps me but here i found out solution.

    Thanks once again.

    Arshad

    Reply
    • Vinay Patel July 3, 2012 at 11:44 AM

      That’s sounds good :) !

      Thanks
      Vinay

      Reply
  • Carlos August 4, 2012 at 7:51 AM

    Thanks, it help me.
    Carlos

    Reply
  • Carlos August 4, 2012 at 7:51 AM

    Thanks, it help me.
    Carlos

    Reply
  • Tauhid Shaikh August 10, 2012 at 10:40 AM

    Thanks…..this solution helped me! :)

    Reply
  • Naresh September 27, 2012 at 4:59 AM

    Nice.

    :)

    Thanks too much.

    Reply
  • SQL Database in Suspect mode | SharePoint and other geeky stuff November 28, 2012 at 2:11 PM

    [...] Recently had a database in suspect mode. I didn’t have a backup of the database. After searching online I found this link. [...]

    Reply
  • WPSNA April 19, 2013 at 3:15 PM

    Thank you very helpful

    Reply

Leave a comment