Me Myself & C#

Manoj Garg’s Tech Bytes – What I learned Today

How to repair a SQL Server 2005 Suspect database

Posted by Manoj Garg on July 17, 2008

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 (‘YourDBname’) 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 ‘yourDBname';

ALTER DATABASE yourDBname SET EMERGENCY

DBCC checkdb(‘yourDBname’)

ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB (‘yourDBname’, REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE yourDBname SET MULTI_USER

and you are done. :)

lightbulbYou should keep one thing in mind while using the above queries that the repair mode used here , REPAIR_ALLOW_DATA_LOSS, is a one way operation i.e. once the database is repaired all the actions performed by these queries can’t be undone. There is no way to go back to the previous state of the database. So as a precautionary step you should take backup of your database before executing above mentioned queries.

Ha-P Querying…

About these ads

121 Responses to “How to repair a SQL Server 2005 Suspect database”

  1. Deepali said

    Can ay one help me to find out reasons of database going to suspect mode,in sql server 2005.

  2. Ben said

    Thanks for this one. Saved me quickly.

  3. Maurizio said

    Thank you very much for the suggested solution!! It worked great

  4. Manoj Garg said

    Glad that it helped you. :)

  5. AdoSK said

    Dakujem, dakujem, dakujem (Slovak language). International – Thank, thank, thank. I’m very happy NOW. I’m thought, that I’m in ass. Thank :)

  6. Mohit Garg said

    Great Man it worked for me as well… :)

  7. saufi said

    thanks, great code

  8. Gilberto said

    I´m brazilian, and used this code and my data base it´s alive! Thanks a lot!! Não tenho mais palavras para agradecer ao postador deste código!! Obrigado mesmo!!! Merece um grande beijo (not gay, please…:))

  9. [...] Neste site encontrei um  procedimento que funcionou perfeitamente! Lembre-se: O risco é seu ao executar o código abaixo! EXEC sp_resetstatus ‘MINHA_TABELA‘; [...]

  10. Alex said

    In this situation advise use this tool because it is very good also it has free status as far as i know-mssql repair,tool
    can help with this problem and retrieve the data, that was considered to be lost,mwill extract housekeeping data from the source database and preview the data, that can be recovered,this tool is a good solution to recover data from corrupted databases in MS SQL Server format,restore databases represent files, like any other documents, they can be easily corrupted by viruses, all sorts of malware, hard drive failures, file system errors, incorrect user actions, etc,supports both data extraction to your hard drive as scripts in SQL format and data export directly to a database in MS SQL Server format.

  11. Srgrn said

    It worked like a charm, great
    Thanks man

  12. prashanth said

    thankyou

  13. sagitariusmzi said

    This solution doesn’t worked for me,
    when i executed the above mention queries, it given me lot of errors ?

  14. Shahid Khan said

    great Code From Great Man it worked Excellent… :)

  15. Deepanshu Jouhari said

    One More Saved, Really required this information on net.

    Thanks

  16. Susannah said

    Saved my life..Thanks…I’ll be taking more regular backups from now on

  17. Munya Chaunza said

    It worked thank you.

  18. Ahsan said

    Saved my life..Thanks…

  19. David said

    Your Query is helpful but sometime when database is badly corrupted then this query is not helpful then during that time you need to use third party tool as SQL Recovery which repair and recover lost data, tables, views, stored procedures, rules, defaults, user defined data types and triggers from Microsoft SQL server database.

  20. Madhu K said

    Hi,

    Thanks for the code, it resolved my DB suspect mode issue.

  21. Rob said

    You saved the day. Thanks :)

  22. Thanks for a great piece of code, saved me an absolute ton of time

    thanks

    Allan

  23. bsking said

    It saved the day for me. thanks a ton.

  24. Antonio said

    thanks, it helped!
    just a notice for novice users: it will delete some records! in my case, i had 4 screwed invoices. after deleting them and all their records (invoice records, stock movement, equipment serial numbers, etc) in the SQL Manager, i was able to proceed with my life.

  25. M Arif Iqbal said

    Dear Friend
    Your code is very simple and very effective it help me a lot. I have resolved many suspect even damage sql server 2005 databases with it. Keep your good work going. With best wishes.
    Sometimes part of the disk is damage or it develop bad block due to this we can not backup or copy the database use http://www.anyreader.com/ to copy the data.

  26. GmE said

    Great code & effective, works fine with me.

    Thanksssssssssssss!

  27. impressive post

  28. Chris A said

    Many thanks Manoj – worked really well for me.

  29. Hey, I read a lot of blogs on a daily basis and for the most part, people lack substance but, I just wanted to make a quick comment to say GREAT blog!…..I”ll be checking in on a regularly now….Keep up the good work! :)

  30. Hey very nice blog!!….I’m an instant fan, I have bookmarked you and I’ll be checking back on a regular….See ya

  31. Alex said

    It worked perfectly! Thanks for it!

  32. TONYE said

    Fantastic page Manoj!
    Worked first time and helped the user get productive in minutes!

  33. This saved my vSphere Virtual Center server database – thanks!

  34. Suresh said

    Cool. I tried all possible way but end up seeing something like “Your DB is in suspect mode and you can’t do this”.

    But, my DB just obeyed your code and its now recovered.

    Great! What I learned today is this!

  35. great post.. helpd a lot..

  36. Eduardo Aguirre said

    Thanks for your help

  37. Renato Luz said

    It’s worked very, very, very well!!!
    Great! Thanks!!! :)

  38. lakhaba said

    Thanks very match
    its work for me !!!!!!!!!!!

  39. Funlove said

    Thank u so much, it’s very useful for me. Thank u!

  40. Mir Khan Brohi said

    Thanks very much it solved my problem

  41. nancy said

    Thank Sir..
    It’s work for me……………..

  42. Hey!!!!!!!,
    Thanks, thanks. Before i used to restore from backup, but with this script, I didn’t loose my data and the problem was solved.

  43. [...] of a suspect SQL DB Found myself needing to repair a suspect SQL db. A really nice post here listed the following [...]

  44. systoolssoftware said

    I tried this but it didn’t worked for me. I then used the sql recovery tool from SysTools. It was able to recover the database without any issue. If anyone has issue with the above solution do check SQL Recovery Tool .

    There is couple of things that we should always ensure that we should keep backing up the database on regular basis. On discussion with their support I found that it is possibility that you may loose certain piece of data though it didn’t happen with me but its a good practice to do so.

    Regards
    Imran Sayed

  45. Clark said

    Thank you very much, this worked like a charm!!!

  46. Valdy said

    Thanks for this post and it helps us recover our development (“temporary”) databases.

  47. Hasi said

    Hey that was great. Thank you so much

  48. Jimmy said

    Thank you, it help me, but i got question.
    When i run the DBCC CheckDB it will show me query complete with error.
    Does this normal or i should take care of the error?

    Because i don’t know what the error mean of, so i just ignore them and run the script until finish.
    But it work 2. So I would like to know what the error all about.

  49. Adrian said

    Graaaaaaaaaaaaaaaaaaciiiiiiiiiiiiiiiaaaaaaaaaaaaaaaaaaas!!!
    Thanks! Thanks! Thanks! Thanks!
    You saved my life and my weekend, hehehe!!

  50. Warnor said

    i have follow your suggestion for recover my corrupted sql database that’s very good, but know another sql recovery software that also repair & recover corrupted mdf file.

    Thanks

  51. Stefan said

    Thanks mate! It worked fine for me! Well done!

  52. João Vieira said

    Thanks man. Worked for me.

    Best regards

  53. Keith said

    This worked for me when I discovered I needed to use [] to surround my database name which had – character in

  54. johnadam said

    There are various reasons that lead to SQL database corruptions, as in:

    Accidental or intentional deletion of data and files
    Invalid database file header
    Internal Program Error(s)
    Crash or failure of hard drive
    if you want repair SQl database than use this tool: http://www.repairsql2000.com/

  55. Kumaresh said

    Thank you. It would be very helpful for me.

  56. Farseer said

    Thank you for this helpful information

  57. Alok Seth said

    It worked great…thanks a ton

  58. Jan said

    That is a great Tip and saved my day

    Thanks

    Jan

  59. Mark said

    I have used your suggestion for sql server recovery which was corrupted due to virus attack. Its save my business.

    Thanks

  60. sajith said

    Thanks alot..done a great job for me…

  61. marlon said

    Worked for me!!! THANKS A LOT! SAVED MY WEEKEND!!

  62. Nallathambi said

    Thanks. It helps me to recover my corrupted Sharepoint Configuration Database.

  63. Mahmoud said

    Thank you very much.
    You saved my time

  64. cactus said

    Worked for me!!! THANKS !

  65. paijo supaijo said

    great!
    it is work!
    thank you :D

  66. Francis said

    Great Code man. This is really a good code n it worked like magic. Thanks.

  67. Tom Sawyer said

    It was a miracle mate.. you did wonderful.. you saved so many people.. you did great!!!!

    Cheers,
    Aaron

  68. wrodrigues said

    thanks brother.

  69. Ty Hackermon said

    Yo…worked like a charm. Very nicely done!

  70. Suman said

    great !!!! it’s worked now Thanks a lot….

  71. Spider21 said

    Thank you for saving me to hell !!!!

  72. Celina said

    Thank you very much.
    You saved my day……

  73. Shaheen said

    Thanks a lot man… It’s works for me.. Thanks again

  74. Osita said

    Hi, what a great script. I love this forum. Three nights of no sleep have been saved.

  75. mwl said

    This is really great. Awesome !!!! Thanks million.

  76. Well I have mixed feeling about this code. I started running the query and it is still running 35 minutes later. Is that normal?

  77. Pasham said

    How long does this code executes??? It has been running now for 9 minutes now on my 14.5GB db…..

    I hope it works successfully…

  78. Rafael said

    Excelente nota

  79. David said

    This is nice, but my there are some files that were not recover. Is there any other thing I can do to have a full recovery of my Database? Somebody, assist

  80. Shobhit said

    You rock, gr8 solution.

  81. Ajeet Rai said

    This is useful…………//

  82. suhandi said

    thx man it’s work :)

  83. chandar said

    Really Superb Thanks a lot for Suggestions. Keep it up

  84. [...] able to bring the database up in emergency mode and start your repair process (checkdb) from there. http://gargmanoj.wordpress.com/2008/07/17/how-to-repair-a-sql-server-2005-suspect-database/ has more details geared toward SQL Server 2005, but I believe they’ll work in 2000 as well [...]

  85. Rénald said

    Thank you, your post saved our day.

  86. manuel said

    dude you are my hero

  87. jjj said

    thanks

  88. Akhil said

    Thanks a lot really its work

  89. amit said

    Thank U very much for valuable response

  90. Utham said

    Awesome, it worked fine for me.

  91. I’m having a database for development purpose. Your solution works perfectly for me.

  92. Amazing! You saved my life

    It Worked fine for me!

    Thanks a lot

  93. majoi said

    Thank you! work for me….

    Zul Malaysia

  94. ECoverBor – Online eCover Creator…

    [...]How to repair a SQL Server 2005 Suspect database « Me Myself & C#[...]…

    • EXEC sp_resetstatus D0011112;

      ALTER DATABASE D0011112 SET EMERGENCY

      DBCC checkdb(D0011112)

      ALTER DATABASE D0011112 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

      DBCC CheckDB (D0011112, REPAIR_ALLOW_DATA_LOSS)

      ALTER DATABASE D0011112 SET MULTI_USER

      D0011112 is suspected database

  95. pba said

    very very helpful … thx a lot

  96. DOEL said

    Thanks,
    Very Very Very Helpfull ……
    My Database OK

  97. Nikhil said

    Thank’s a lot Manoj
    u saved me…. :)

  98. [...] http://www.nucleusdatarecovery.org/sqlpasswordrecovery.html http://gargmanoj.wordpress.com/2008/07/17/how-to-repair-a-sql-server-2005-suspect-database/ http://www.sql2008.databaserecovery.org/ This entry was posted in SQL by admin. Bookmark the [...]

  99. [...] Table error: Table ” (ID ) for these countries as Brazil Russia China Continental Europe back in 1990th the novice developers to be complete documentation provided for both the PHP and MySQL is popular [...]

  100. [...] http://www.qweas.com/guide/how_to/how_to_recover_sql_database.htm http://gargmanoj.wordpress.com/2008/07/17/how-to-repair-a-sql-server-2005-suspect-database/ This entry was posted in SQL by admin. Bookmark the [...]

  101. [...] http://forums.realgm.com/boards/viewtopic.php?f=40&t=1152104 http://gargmanoj.wordpress.com/2008/07/17/how-to-repair-a-sql-server-2005-suspect-database/ http://www.repairsqldatabase.org/ms-sql-table-recovery-tool This entry was posted in SQL by [...]

  102. Web Development London UK…

    [...]How to repair a SQL Server 2005 Suspect database « Me Myself & C#[...]…

  103. Windows 8 Keyboard Shortcuts…

    [...]How to repair a SQL Server 2005 Suspect database « Me Myself & C#[...]…

  104. [...] http://gargmanoj.wordpress.com/2008/07/17/how-to-repair-a-sql-server-2005-suspect-database/ [...]

  105. i used

    EXEC sp_resetstatus D0011112;

    ALTER DATABASE D0011112 SET EMERGENCY

    DBCC checkdb(D0011112)

    ALTER DATABASE D0011112 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    DBCC CheckDB (D0011112, REPAIR_ALLOW_DATA_LOSS)

    ALTER DATABASE D0011112 SET MULTI_USER

    D0011112 is Suspected Database name

    it works thank you very much…

  106. JSQ said

    Good tip and working!

  107. Ranga said

    Thanks a lot man !!!!!!!!

  108. Rahul said

    Thanks a lot……….!!!!!!!! Great Job……..!!!

    Rahul Badwaik

  109. Daniel said

    Excelent!!! You saved my life!

  110. sach said

    Great Man it worked for me as well… :)

  111. Alejandro said

    You…. rules! It save my life

  112. Pramod kumar singh said

    Thanks a lot……..

    I’ve been a trouble because of suddenly two of my database has been marked as suspect and your posted content worked for me….

  113. sameera said

    Thanks ………Its work…..:)

  114. Karthick said

    great !!!! it’s worked now Thanks a lot…

  115. Thanks. It salved my database. Great job and contribution. God bless you!

  116. Ricky said

    The only thing that could have made this better is if you had included:

    ALTER DATABASE YourDBName SET RECOVERY FULL

  117. OGBM said

    Merci j’ai pu reparer ma base de donnees

  118. RAJ KUMAR said

    Thanks….great…bacha liya..aapki command ne…
    thanks you very much
    RAJKUMAR

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

 
Follow

Get every new post delivered to your Inbox.

Join 410 other followers

%d bloggers like this: