Repair SQL Server 2008 R2 Suspected Database

Posted by Vinay Patel | Apr 7, 2011 | SQL Server | 46 |

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.

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.

About The Author

Vinay Patel

I’m not a tech-savvy, But doing this for my future reference. This might just help someone out there who might have encountered the same thing as i did. Thanks for visiting my site - Vinay Patel

46 Comments

BAHRUZ on August 8, 2011 at 8:01 AM

THANK YOU
Vinay Patel on September 1, 2011 at 4:41 PM

You Welcome Bahruz !

Thanks
Vinay
Prett Sons on 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.
Frances on 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.
Anonymous on May 10, 2012 at 6:08 PM

This is an excellent post. This is all I needed to fix the issue.
Thanks a lot.
Antonio Assuncao on May 11, 2012 at 11:17 AM

Nice tip!

Thanks…
(Y)
Vinay Patel on May 11, 2012 at 1:28 PM

Glad to hear that it helps you all !

Thanks
Vinay
Sabuni Joseph on June 7, 2012 at 8:19 AM

This is amazing thanks men. it fixed ma problem.
- Vinay Patel on June 7, 2012 at 8:27 AM
  
  I am Glad to hear that !
developer on June 27, 2012 at 5:23 AM

Thanx patel

this fix the problem
Arshad on 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
- Vinay Patel on July 3, 2012 at 11:44 AM
  
  That’s sounds good 🙂 !
  
  Thanks
  Vinay
Carlos on August 4, 2012 at 7:51 AM

Thanks, it help me.
Carlos
Carlos on August 4, 2012 at 7:51 AM

Thanks, it help me.
Carlos
Tauhid Shaikh on August 10, 2012 at 10:40 AM

Thanks…..this solution helped me! 🙂
Naresh on September 27, 2012 at 4:59 AM

Nice.

🙂

Thanks too much.
WPSNA on April 19, 2013 at 3:15 PM

Thank you very helpful
Shuja on June 12, 2013 at 5:33 AM

Dear

Thank you for your help can you please let me now that how much time it will take to resolve my database from suspect to normal way?
- Vinay Patel on June 12, 2013 at 6:01 AM
  
  Hi ,
  
  I am not sure what parameters are affecting in recovery from suspected mode – in my scenario – it was 6 GB database and took about 20 minutes.
  
  Thanks
  Vinay
  - Shuja on June 12, 2013 at 9:00 AM
    
    Actually Vinay my query till executing from 24 hours now can you please suggest
    me what should be my next step?
    - Vinay Patel on June 12, 2013 at 11:35 AM
      
      What is your DB size ?
      What is recovery model of your database ?
      
      There are total 6 Queries – how many of them are got completed successfully ?
      
      If you can shrink your log file in mean time shrink out your Log
      - Shuja on June 12, 2013 at 11:44 AM
        
        My DB size is 24GB and i have applied this query
        EXEC sp_resetstatus ‘ServiceManager’;
        
        ALTER DATABASE ServiceManager SET EMERGENCY
        
        DBCC checkdb(‘ServiceManager’)
        
        ALTER DATABASE ServiceManager SET SINGLE_USER WITH ROLLBACK IMMEDIATE
        
        DBCC CheckDB (‘ServiceManager’, REPAIR_ALLOW_DATA_LOSS)
        
        ALTER DATABASE ServiceManager SET MULTI_USER
        
        and now i am at this one
        DBCC CheckDB (‘ServiceManager’, REPAIR_ALLOW_DATA_LOSS)
        
        above 4 queries has been completed now i am at DBCC’s query.
      - Vinay Patel on June 12, 2013 at 11:53 AM
        
        Things are going right way – it seems your log is not committed and there are must be non- committed running transaction
        
        Your db is ready – just let it finish DBcheck to make sure after recovery data integrity is perfect !
Mac on June 24, 2013 at 10:11 AM

BIG Thanks… worked for e as well…
- Vinay Patel on June 24, 2013 at 10:20 AM
  
  Great to hear that 🙂 !
DJ on August 1, 2013 at 10:02 AM

Thanks so much,

I was even scared with the allow_data_loss
But was surprised when I got my data.
Thanks Mr Patel
- Vinay Patel on August 1, 2013 at 10:40 AM
  
  Great to hear that it helped you too !
  
  Thanks
  Vinay
Dan K on August 19, 2013 at 3:15 PM

This post has bailed me out twice in the past week now. Thanks very much!
Any ideas why a db might enter “suspect” mode in the first place?
Jerry Pyne on August 22, 2013 at 6:49 PM

Thanks for the approach and comments but in the spirit of sharing I had a different problem that was simply solved by recycling the server.
The problem was my fault because it was caused by running out of disk space.
Disk space was added (by deleting old backups) but the Suspect did not get cleared using the above commands… Because of an unrelated problem the First alter failed (by the way you should enter the first two commands and get the results BEFORE executing the third command).

Because of an unrelated problem and the first three commands ran forever (and there was NO disk or CPU activity). The stop button uncovered the failed alter. The “unrelated problem was associated with our tempdb growth and time outs in attempts to read the database.

Restarting the SQL server fixed both problems but I would only do that if you get time outs on the tempdb and there is no I/O activity on the database.

Good luck.
- Vinay Patel on August 27, 2013 at 8:51 AM
  
  Thanks for feedback !
  
  Actually , I tried to draft only the scenario what I have come across ! , And definitely will make sure to cover up all other aspects too
  
  Thanks
  Vinay
Eryck Salinas on October 22, 2013 at 4:33 PM

Excellent tip, works perfectly!

Thanks for your help.
- Vinay Patel on October 22, 2013 at 5:03 PM
  
  Great to hear that it helped you too !
  
  Thanks
  Vinay
Edwin Daza on November 21, 2013 at 3:00 PM

You saved my life, Thanks!
Ashish on December 9, 2013 at 7:27 AM

Thank You..
- Vinay Patel on December 9, 2013 at 7:32 AM
  
  Great to hear that it helped you too !
  
  Thanks
  Vinay
Vijay Shrestha on December 19, 2013 at 8:14 AM

Thank you very much above script works for me…!!!:)..
- Vinay Patel on December 19, 2013 at 8:17 AM
  
  Excellent 🙂 !!
  
  Thanks
  Vinay
Marrina on August 11, 2014 at 6:06 AM

I used the above Queries to recover my database from Suspected mode, but when I execute
DBCC checkdb(‘SGERP_EMPTYDB’) query I get the below error:

Check statement terminated due to unrepairable error.

But the database was recovered from Suspected mode but in this case I cant able to recover the datas

Please help me (thanks in advance)
Raju on December 25, 2014 at 11:30 AM

Just Awesome Vinay ,, this is really very much Helpful to Repair the SQL Database..

Give your mail ID

Thnx,
Raju
- Vinay Patel on February 13, 2015 at 7:39 AM
  
  Glad to hear that 🙂
  
  Thanks
  Vinay
Dave Rolph on July 2, 2015 at 5:12 PM

I can’t thank you enough for posting this, like many other before me I tried a lot of googling and could not find an answer and was about to give up, I have been working hard on a new project and taking regular backups but lost 15 hours of work – which looks like is now back !

Your the man ! thank you again. I will sleep tonight
- Vinay Patel on July 23, 2015 at 5:26 PM
  
  I am glad it worked out for you 🙂 !
Chandan on July 27, 2015 at 8:51 AM

Thanks friend. It was awesome & helped me a lot in crisis period.
- Vinay Patel on August 7, 2015 at 7:52 AM
  
  I am glad it worked out for you 🙂 !
jaideep sarkar on February 4, 2017 at 9:36 AM

Hi Vinay,
This is in reference to the critical issue we had with MS SQL 2008R2 Cluster database.
We are having eight nos. of instances installed on SQL cluster with 2 no. of cluster nodes.
But recently we had got two of eight instances corrupted . And the SQL services are not getting online for only two instances & database attached with two instances got blanked out.
Pls help out of it , this is a nightmare to us.
- Vinay Patel on February 7, 2017 at 7:12 AM
  
  Can you provide further information
  1# if log shipping is enabled on each instance ?
  2# do you have backup scheduled per node ?
  
  Please share further technical information
  
  Thanks
  Vinay

About Vinay Patel

I’m not a tech savvy, But doing this for my future reference. This might just help someone out there who might have encountered the same thing as i did.

Thanks for visiting my site

- Vinay Patel