Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A guide to recover a database out from Suspect mode


A guide to recover a database out from Suspect mode

Author
Message
Paul Randal
Paul Randal
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2171 Visits: 1714
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
DMarvez
DMarvez
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
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
TravisDBA
TravisDBA
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1480 Visits: 3069
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! Smile

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
r.armstrong-finnerty
r.armstrong-finnerty
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 341
Hi Gail,

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

Cheers!
ravi.levtech
ravi.levtech
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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
Elliswhite
Elliswhite
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 54
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search