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. š
You 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…
Deepali said
Can ay one help me to find out reasons of database going to suspect mode,in sql server 2005.
bijendra rajput said
yes i can help you.
A database can go in suspect mode for many reasons like improper shutdown of the database server, corruption of the database files etc.
Pravin Madalia Parmar said
Hi all,
This is really good solution.
Thanks,
Ben said
Thanks for this one. Saved me quickly.
Maurizio said
Thank you very much for the suggested solution!! It worked great
Manoj Garg said
Glad that it helped you. š
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 š
Mohit Garg said
Great Man it worked for me as well… š
saufi said
thanks, great code
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…:))
Reparar uma tabela marcada como suspect no SQL Server 2005 | David Ruiz said
[…] Neste site encontrei umĀ procedimento que funcionou perfeitamente! Lembre-se: O risco Ć© seu ao executar o cĆ³digo abaixo! EXEC sp_resetstatus ‘MINHA_TABELA‘; […]
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.
Srgrn said
It worked like a charm, great
Thanks man
prashanth said
thankyou
sagitariusmzi said
This solution doesn’t worked for me,
when i executed the above mention queries, it given me lot of errors ?
Shahid Khan said
great Code From Great Man it worked Excellentā¦ š
Deepanshu Jouhari said
One More Saved, Really required this information on net.
Thanks
Susannah said
Saved my life..Thanks…I’ll be taking more regular backups from now on
Munya Chaunza said
It worked thank you.
Ahsan said
Saved my life..Thanksā¦
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.
Madhu K said
Hi,
Thanks for the code, it resolved my DB suspect mode issue.
Rob said
You saved the day. Thanks š
Allan Connolly said
Thanks for a great piece of code, saved me an absolute ton of time
thanks
Allan
bsking said
It saved the day for me. thanks a ton.
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.
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.
GmE said
Great code & effective, works fine with me.
Thanksssssssssssss!
aasim abdullah said
impressive post
Chris A said
Many thanks Manoj – worked really well for me.
Online Stock Investing said
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! š
Online Stock Trading said
Hey very nice blog!!….I’m an instant fan, I have bookmarked you and I’ll be checking back on a regular….See ya
Alex said
It worked perfectly! Thanks for it!
TONYE said
Fantastic page Manoj!
Worked first time and helped the user get productive in minutes!
gVa Solutions said
This saved my vSphere Virtual Center server database – thanks!
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!
Ramani Sandeep said
great post.. helpd a lot..
Eduardo Aguirre said
Thanks for your help
Renato Luz said
It’s worked very, very, very well!!!
Great! Thanks!!! š
lakhaba said
Thanks very match
its work for me !!!!!!!!!!!
Funlove said
Thank u so much, it’s very useful for me. Thank u!
Mir Khan Brohi said
Thanks very much it solved my problem
nancy said
Thank Sir..
It’s work for me……………..
Jide Abolarin said
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.
Repair of a suspect SQL DB « Are you on the bus? said
[…] of a suspect SQL DB Found myself needing to repair a suspect SQL db. A really nice post here listed the following […]
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
Clark said
Thank you very much, this worked like a charm!!!
Valdy said
Thanks for this post and it helps us recover our development (“temporary”) databases.
Hasi said
Hey that was great. Thank you so much
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.
Adrian said
Graaaaaaaaaaaaaaaaaaciiiiiiiiiiiiiiiaaaaaaaaaaaaaaaaaaas!!!
Thanks! Thanks! Thanks! Thanks!
You saved my life and my weekend, hehehe!!
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
Stefan said
Thanks mate! It worked fine for me! Well done!
JoĆ£o Vieira said
Thanks man. Worked for me.
Best regards
Keith said
This worked for me when I discovered I needed to use [] to surround my database name which had – character in
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/
Kumaresh said
Thank you. It would be very helpful for me.
Farseer said
Thank you for this helpful information
Alok Seth said
It worked great…thanks a ton
Jan said
That is a great Tip and saved my day
Thanks
Jan
Mark said
I have used your suggestion for sql server recovery which was corrupted due to virus attack. Its save my business.
Thanks
sajith said
Thanks alot..done a great job for me…
marlon said
Worked for me!!! THANKS A LOT! SAVED MY WEEKEND!!
Nallathambi said
Thanks. It helps me to recover my corrupted Sharepoint Configuration Database.
Mahmoud said
Thank you very much.
You saved my time
cactus said
Worked for me!!! THANKS !
paijo supaijo said
great!
it is work!
thank you š
Francis said
Great Code man. This is really a good code n it worked like magic. Thanks.
Tom Sawyer said
It was a miracle mate.. you did wonderful.. you saved so many people.. you did great!!!!
Cheers,
Aaron
wrodrigues said
thanks brother.
Ty Hackermon said
Yo…worked like a charm. Very nicely done!
Suman said
great !!!! it’s worked now Thanks a lot….
Spider21 said
Thank you for saving me to hell !!!!
Celina said
Thank you very much.
You saved my day……
Shaheen said
Thanks a lot man… It’s works for me.. Thanks again
Osita said
Hi, what a great script. I love this forum. Three nights of no sleep have been saved.
mwl said
This is really great. Awesome !!!! Thanks million.
Cyber Bullying Report said
Well I have mixed feeling about this code. I started running the query and it is still running 35 minutes later. Is that normal?
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…
Rafael said
Excelente nota
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
Shobhit said
You rock, gr8 solution.
Ajeet Rai said
This is useful…………//
suhandi said
thx man it’s work š
chandar said
Really Superb Thanks a lot for Suggestions. Keep it up
Getting data out of a Suspect database - Admins Goodies said
[…] able to bring the database up in emergency mode and start your repair process (checkdb) from there. https://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 […]
RĆ©nald said
Thank you, your post saved our day.
manuel said
dude you are my hero
jjj said
thanks
Akhil said
Thanks a lot really its work
amit said
Thank U very much for valuable response
Utham said
Awesome, it worked fine for me.
Low Yee Wee (@lowyeewee) said
I’m having a database for development purpose. Your solution works perfectly for me.
Rubens Arandas said
Amazing! You saved my life
It Worked fine for me!
Thanks a lot
majoi said
Thank you! work for me….
Zul Malaysia
ECoverBor - Online eCover Creator said
ECoverBor – Online eCover Creator…
[…]How to repair a SQL Server 2005 Suspect database « Me Myself & C#[…]…
Pradeep Patel said
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
pba said
very very helpful … thx a lot
DOEL said
Thanks,
Very Very Very Helpfull ……
My Database OK
Nikhil said
Thank’s a lot Manoj
u saved me…. š
How To Fix Fix Corrupt Sqlite Database Errors said
[…] http://www.nucleusdatarecovery.org/sqlpasswordrecovery.html https://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 […]
How To Fix Restoring Sql Database Backup Errors said
[…] 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 […]
How To Fix Sql Database Connection Close Errors said
[…] http://www.qweas.com/guide/how_to/how_to_recover_sql_database.htm https://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 […]
How To Fix Sql Database Development Questions Errors said
[…] http://forums.realgm.com/boards/viewtopic.php?f=40&t=1152104 https://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 […]
Web Development London UK said
Web Development London UK…
[…]How to repair a SQL Server 2005 Suspect database « Me Myself & C#[…]…
Windows 8 Keyboard Shortcuts said
Windows 8 Keyboard Shortcuts…
[…]How to repair a SQL Server 2005 Suspect database « Me Myself & C#[…]…
How to repair a SQL Server 2005 Suspect database « Sulich's Blog said
[…] https://gargmanoj.wordpress.com/2008/07/17/how-to-repair-a-sql-server-2005-suspect-database/ […]
Pradeep Patel said
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…
JSQ said
Good tip and working!
Ranga said
Thanks a lot man !!!!!!!!
Rahul said
Thanks a lot……….!!!!!!!! Great Job……..!!!
Rahul Badwaik
Daniel said
Excelent!!! You saved my life!
sach said
Great Man it worked for me as wellā¦ š
Alejandro said
You…. rules! It save my life
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….
sameera said
Thanks ………Its work…..:)
Karthick said
great !!!! itās worked now Thanks a lotā¦
Gilberto Campos said
Thanks. It salved my database. Great job and contribution. God bless you!
Ricky said
The only thing that could have made this better is if you had included:
ALTER DATABASE YourDBName SET RECOVERY FULL
OGBM said
Merci j’ai pu reparer ma base de donnees
RAJ KUMAR said
Thanks….great…bacha liya..aapki command ne…
thanks you very much
RAJKUMAR