How to resolve SQL Suspect issues for SQL 2008 R2 server

Article
10/23/2013

Question

_{Wednesday, October 23, 2013 10:11 AM}

Unfortunately, while doing application update the server got restarted and the SQL DB gone in to suspect mode. I've seen loads of resolution around the web. I would like to know whether there could be any impact or data loss if I use following commands ?

Please advice!

I ran the following command but it didn't work

DBCC CHECKDB (‘CM_CAS’) WITH NO_INFOMSGS, ALL_ERRORMSGS

Msg 926, Level 14, State 1, Line 1
Database 'CM_CAS' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.

Checked error log without much success.

Do you recommend to run the following commands ...please suggest

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

Anoop C Nair - @anoopmannur :: **MY Site: **www.AnoopCNair.com :: **FaceBook: **ConfigMgr(SCCM) Page :: **Linkedin: **Linkedin<

All replies (16)

_{Tuesday, November 5, 2013 8:40 AM ✅Answered}

The following was my resolution to the issue.

http://anoopcnair.com/2013/11/01/configmgr-sccm-2012-r2-upgrade-put-sql-db-suspect-mode/

Anoop C Nair - @anoopmannur :: **MY Site: **www.AnoopCNair.com :: **FaceBook: **ConfigMgr(SCCM) Page :: **Linkedin: **Linkedin<

_{Wednesday, October 23, 2013 10:27 AM | 1 vote}

perfect but check the database status using

select DATABASEPROPERTYEX('dbname','status')make sure that there is no issue in the SAN. Rest every thing is what you wrote

Ramesh Babu Vavilla MCTS,MSBI

_{Wednesday, October 23, 2013 10:37 AM | 1 vote}

Hello,

I would never run repair_allow_data_loss (unless i dont have a backup) if i really care about my data.if you run it it will delete data and try to recover your database which is also not guarenteed that it will recover.It will also not tell you what data has been deleted

So do you have latest valid backup.Try restoring from backup, its the best way to recover from corruption.

Also if your DB is in suspect mode you cannot run checkdb as its still goging in recovery will now allow any transaction to run unless it recovers .

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

_{Wednesday, October 23, 2013 10:40 AM}

perfect but check the database status using

select DATABASEPROPERTYEX('dbname','status')make sure that there is no issue in the SAN. Rest every thing is what you wrote

Thank you Babu !

I checked the above query and confirmed that it's in SUSPECT mode.

Please let me know whether there will any data loss if I run other set of commands.

Also, how much time it will take to complete these commands for 7 GB DB?

Very much appreciate your time and answers !!!

Anoop C Nair - @anoopmannur :: **MY Site: **www.AnoopCNair.com :: **FaceBook: **ConfigMgr(SCCM) Page :: **Linkedin: **Linkedin<

_{Wednesday, October 23, 2013 10:45 AM}

Hello,

I would never run repair_allow_data_loss if i really care about my data.if you run it it will delete data and try to recover your database which is also not guarenteed that it will recover.

So do you have latest valid backup.Try restoring from backup, its the best way to recover from corruption.

Hello ! - Thank you for the reply.

I don't have backup :( (saddest part of it).....

When you say it will delete all the data means = There won't be any data if at all recover it or there is a chance of getting my data back (at least 50% of the original) ?

Anoop C Nair - @anoopmannur :: **MY Site: **www.AnoopCNair.com :: **FaceBook: **ConfigMgr(SCCM) Page :: **Linkedin: **Linkedin<

_{Wednesday, October 23, 2013 10:58 AM | 1 vote}

Hello,

I would never run repair_allow_data_loss if i really care about my data.if you run it it will delete data and try to recover your database which is also not guarenteed that it will recover.

So do you have latest valid backup.Try restoring from backup, its the best way to recover from corruption.

Hello ! - Thank you for the reply.

I don't have backup :( (saddest part of it).....

When you say it will delete all the data means = There won't be any data if at all recover it or there is a chance of getting my data back (at least 50% of the original) ?

Anoop C Nair - @anoopmannur :: **MY Site: **www.AnoopCNair.com :: **FaceBook: **ConfigMgr(SCCM) Page :: **Linkedin: **Linkedin<

Hello,

I did not said all data I said it will delete data (means some data) and its not guaranteed that your database will come online or will be able to recover.

Its extremely sad to hear that you have not back.I really dont have much idea how to recover database without backup when it is marked suspect.May be as last option you will have to run Repair allow data loss but you wont be able to run it either if your db is suspect it wont allow you to run it.

But hold your horses you might want to get some more expert comment on this.

May be there is chance we recover some data from it using third party tool

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

_{Wednesday, October 23, 2013 11:13 AM}

Ok, I'll hold my horses for some time :-D

Anoop C Nair - @anoopmannur :: **MY Site: **www.AnoopCNair.com :: **FaceBook: **ConfigMgr(SCCM) Page :: **Linkedin: **Linkedin<

_{Wednesday, October 23, 2013 11:36 AM | 1 vote}

Ok, I'll hold my horses for some time :-D

Anoop C Nair - @anoopmannur :: **MY Site: **www.AnoopCNair.com :: **FaceBook: **ConfigMgr(SCCM) Page :: **Linkedin: **Linkedin<

Hello,

I would like you to read and follow steps in Paul article.

http://www.sqlskills.com/blogs/paul/disaster-recovery-101-fixing-metadata-corruption-without-a-backup/

total list of various scenarios in below article

http://www.sqlskills.com/blogs/paul/category/corruption/

HTH

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

_{Wednesday, October 23, 2013 11:49 AM}

Its extremely sad to hear that you have not back.

I found another copy of DB files CM_CAS.LDF and CM_CAS_log.LDF. Does this help to restore the DBs? Please suggest.

Anoop C Nair - @anoopmannur :: **MY Site: **www.AnoopCNair.com :: **FaceBook: **ConfigMgr(SCCM) Page :: **Linkedin: **Linkedin<

_{Wednesday, October 23, 2013 11:54 AM | 1 vote}

Its extremely sad to hear that you have not back.

I found another copy of DB files CM_CAS.LDF and CM_CAS_log.LDF. Does this help to restore the DBs? Please suggest.

Anoop C Nair - @anoopmannur :: **MY Site: **www.AnoopCNair.com :: **FaceBook: **ConfigMgr(SCCM) Page :: **Linkedin: **Linkedin<

Hello ,

Yes you can try to attach it using below command

use mastergoCREATE DATABASE MyAdventureWorks     ON (FILENAME = 'C:\MySQLServer\AdventureWorks_Data.mdf'),     (FILENAME = 'C:\MySQLServer\AdventureWorks_Log.ldf')     FOR ATTACH; --locations and name can be put as per location of your Data and log file

You can use GUI also

RK on database...attacheclick on add and point location to MDF and ldf files and click ok

HOPE they are consistent data and log files

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

_{Wednesday, October 23, 2013 12:36 PM}

I hope, I need to dettach the SUSPECT db before attaching these DB files ?

Anoop C Nair - @anoopmannur :: **MY Site: **www.AnoopCNair.com :: **FaceBook: **ConfigMgr(SCCM) Page :: **Linkedin: **Linkedin<

_{Wednesday, October 23, 2013 12:47 PM | 1 vote}

I hope, I need to dettach the SUSPECT db before attaching these DB files ?

Anoop C Nair - @anoopmannur :: **MY Site: **www.AnoopCNair.com :: **FaceBook: **ConfigMgr(SCCM) Page :: **Linkedin: **Linkedin<

Hello,

You can but please dont detach it .

You can attach current database with different name and also files on location which is different from already present.After you attach successfully look for data in your newly created DB .If its ok from your side then delete old one and rename the new DB to old name.

Like you can attach new db with name CM_CAS_new and location of log and data files on some other folder may be D:\Newfolder

CREATE DATABASE CAM_CAS_NEW    ON (FILENAME = 'D:\Newfolder\AdventureWorks_Data.mdf'),     (FILENAME = 'D:\Newfolder\AdventureWorks_Log.ldf')     FOR ATTACH;

HTH

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

_{Monday, November 4, 2013 7:14 AM}

Shanky, please check out this thread: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/64ad4f52-2fd8-4266-b4a4-5657c8870246/needed-more-answerers?forum=sqlgetstarted

Thanks!

Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

Answer an interesting question? Create a wiki article about it!

_{Tuesday, November 5, 2013 8:55 AM}

The following was my resolution to the issue.

http://anoopcnair.com/2013/11/01/configmgr-sccm-2012-r2-upgrade-put-sql-db-suspect-mode/

Anoop C Nair - @anoopmannur :: **MY Site: **www.AnoopCNair.com :: **FaceBook: **ConfigMgr(SCCM) Page :: **Linkedin: **Linkedin<

Hello Anoop,

Thanks a lot for your feedback but IMHO you should never restart your SQL server instance when DB is in suspect mode.From your post it seems to me as Hardware issue as restart cannot,AFAIT, bring back suspect DB in online mode.I went through your link.

Did you also ran recover allow data loss script that might have brought your DB online.

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

_{Tuesday, November 5, 2013 12:57 PM}

My SQL team

suggested to restart the server. I didn't run recover allow data loss script.

Anoop C Nair - @anoopmannur :: **MY Site: **www.AnoopCNair.com :: **FaceBook: **ConfigMgr(SCCM) Page :: **Linkedin: **Linkedin<

_{Tuesday, November 5, 2013 1:07 PM}

My SQL team

suggested to restart the server. I didn't run recover allow data loss script.

Anoop C Nair - @anoopmannur :: **MY Site: **www.AnoopCNair.com :: **FaceBook: **ConfigMgr(SCCM) Page :: **Linkedin: **Linkedin<

I would suggest you to get the Hard disks analyzed.And please if ever you see SQL database in suspect mode don't restart SQL server you might loose chance of recovering it.Also never detach a suspect database.

See common Myth( restarting removes corruption from DB) busted by Paul Randal

http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2130-corruption-can-be-fixed-by-restarting-sql-server/

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

Connect, code, and grow

Share via

Question

All replies (16)

Share via

How to resolve SQL Suspect issues for SQL 2008 R2 server

Question

All replies (16)

Additional resources