April 1, 2010 at 5:41 am
If u have backups then restore that dB.
one good link which solves this suspect mode is
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/06/619304.aspx
🙂
Cheers!
Sasidhar P
April 2, 2010 at 3:26 pm
This should be your last solution if nothing else work.
The first thing to do is to find the cause of the problem by diving in the logs and then take the course of actions necessary.
If there no fast or easy solution then I will prefer restoring the DB from backup of backups are done regularly. This is sometimes more secure than a repair with the REPAIR_ALLOW_DATA_LOSS option;
because you know that you have a consistant and working DB at the end of the process.
Hope this helps.
Regards
Richie
May 22, 2014 at 4:52 am
1. Change the status of your database. Suppose database name is “BluechipDB”
EXEC sp_resetstatus '';
Example: EXEC sp_resetstatus 'BlueChipDB'
2. Set the database in “Emergency” mode
ALTER DATABASE <Database Name> SET EMERGENCY;
Example: ALTER DATABASE BlueChipDB SET EMERGENCY
3. Check the database for any inconsistency
DBCC CHECKDB('');
Example: DBCC checkdb('BlueChipDB')
4. If you get any error after executing DBCC CHECKDB then immediately bring the database in SINGLE USER MODE by running following query. If no error found then you need not execute the following query.
ALTER DATABASE <Database Name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
Example: ALTER DATABASE BlueChipDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
5. For safety, take the backup of the database.
6. Run the following query as next step. Remember while using the below query, that uses REPAIR_ALLOW_DATA_LOSS, is a one way operation that is 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 in step 5 mentioned above.
DBCC CHECKDB ('', REPAIR_ALLOW_DATA_LOSS);
Example: DBCC CheckDB ('BlueChipDB', REPAIR_ALLOW_DATA_LOSS)
7. Finally, bring the database in MULTI USER mode
ALTER DATABASE <Database Name> SET MULTI_USER;
ALTER DATABASE [BlueChipDB] SET MULTI_USER
8. Refresh your database server and verify the connectivity of your database. Now users should be able to connect to the database properly. If any data loss, you can restore database – backup taken in step 5.
SSMS Expert
May 22, 2014 at 5:01 am
Please note: 4 year old thread, and repair allow data loss should never be the first suggestion and should never be suggested without an explanation of the effects.
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
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply