Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345

A guide to recover a database out from Suspect mode Expand / Collapse
Author
Message
Posted Thursday, February 11, 2010 8:41 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:48 AM
Points: 2,040, Visits: 1,667
Well of course they do - no-one was saying SQL Server is infallible - just that it doesn't fail in the way described.


Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Post #864113
Posted Thursday, February 11, 2010 8:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 12:13 PM
Points: 41, Visits: 313
Cars break (or don't brake, thank you Toyota, Honda, Ford), planes break (Boeing, Airbus), my bones have been broken (Mom).

It all comes down to using the best practices to try and keep everything humming along and minimizing the hicups that all software, hardware will eventually suffer.

Having a preventive process in place and the resources, knowledge and processes to lean on when something does break is the key to restful nights and happy
bosses and customers.

Dave

Post #864121
Posted Thursday, February 11, 2010 9:09 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:03 PM
Points: 1,334, Visits: 3,069
Malcolm Daughtree (2/10/2010)
The more I read on this thread the more worried I become... do you actually believe this ...?

RUNNING OUT OF DISK SPACE WILL NOT SEND YOUR DATABASES SUSPECT.

Come on guys read Books on line, buy an Admin guide, heck fill a disk drive and see what happens. Thankfully its comments like these that keep employers worried and me employeed.


That was not my point. Whatever the failure is or isn't, or as Paul basically states "it doesn't fail in the described way" that is still no reason to not check for available apace left on drives regularly. It can head off a lot of unexpected issues from occurring. That is what I was trying to put forth. It's not so much about what exactly causes or doesn't cause a failure, it's more about installing proactive measures like I stated above to prevent them, or at least notify someone beforehand, in the first place. For example, your car engine can fail for a myriad of reasons (most of them you may not even know), and many of them may have nothing to do with dirty oil, but it would still be foolhardy for anyone to not change the oil regularly.. That is just common sense. So, regardless of whether a full drive causes a suspect database or not in any version of SQL Server, doesn't really matter, even so still check the available space on those drives regularly! :)


"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ..."
Post #864130
Posted Monday, October 21, 2013 2:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 1:37 AM
Points: 6, Visits: 152
Hi Gail,

Your nice-n-simple Offline/Online worked perfectly for us when we had a DB in "Recovery Pending" state.

Cheers!
Post #1506587
Posted Tuesday, March 11, 2014 3:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 3:32 AM
Points: 1, Visits: 0
Hi Prasad,

Please help me the complete details in code format.I'm very new to Sql Server.

Appreciate your help on this..

Regards
Ravi Theja
Post #1549612
Posted Thursday, May 22, 2014 4:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 12:50 AM
Points: 41, Visits: 53
Follow simple steps to recover sql database suspect mode
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
Post #1573497
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse