May 16, 2011 at 12:21 pm
Hi all,
One my db went to suspect mode size(250GB)......i just follow these steps
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
---------------------
Still my db suspect in mode i'm not able access is there any way to retrive my db online
May 16, 2011 at 1:28 pm
What you did there is the absolute last resort for suspect databases, not the first thing you should do. You may well have messed things up worse.
SQL error log. There will be messages saying why the DB is suspect. Post them here.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 16, 2011 at 9:02 pm
Did you get some errors after executing the DBCC checkdb.
May i know why did you executed DBCC checkdb with allow_data_loss option. As Gail mentioned above this was the last resort. we should rather avoid this option because it would sometimes cause huge data loss.
Is it a production database iF Yes? Don't you have Backups is place?
Regards,
Sachin Sharma
May 18, 2011 at 6:11 am
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 the error:
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
Please note that with REPAIR_ALLOW_DATA_LOSS option, keep in mind that some cases will loose some data. That’s why it’s good idea to have a backup regularly eventhough just for development or testing environment.
May 18, 2011 at 6:31 am
SUPER SQL STAR (5/18/2011)
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
*screams out loud*
No. No. No. No. No!!!!
That is not EVER the first, default action on a corrupt database. That is the absolute last resort, the thing you do if everything else has failed and you have no backup at all. Never recommend that as a default action, especially when you have NO idea of what's actually wrong!!!
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 18, 2011 at 6:43 am
I wish they know you can actually snap a man in 2 with all your martial arts training.
Not sure they'd do the right thing and post un-dangerous answers... but still :w00t:
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply