Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Database in Emergency Mode (bypass recovery)
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

petemill66
Starting Member

USA
1 Posts

Posted - 11/21/2007 :  11:47:26  Show Profile  Reply with Quote
1st question I have to ask is:
Is Emergency Mode the same as Bypass Recovery Mode?

2nd:
I have a SQL2005 Database that was suspect this morning. I put the database in Emergency Mode: alter database {dbname} set emergency.

I did a dbcc checkdb on the database and found some corrupt indexes. I attempted to do a dbreindex on the table and got the error:
Could not run BEGIN TRANSACTION in database '{dbname}' because the database is in bypass recovery mode.

How can I put the database in single_user mode and to a point where I can reindex the 1 table that is causing the problems. I would even be willing to drop the indexes and re-create.

Any suggestions or help would be appreciated!
Thanks in advance.

Pete

Edited by - petemill66 on 11/21/2007 11:48:02

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 11/21/2007 :  16:30:13  Show Profile  Reply with Quote
Db is read only when in emergency mode. Rebooting server may solve db suspect caused by hardware issue. Check windows event log for any hardware related error, fix them and reboot.
Go to Top of Page

Bishwas
Starting Member

India
1 Posts

Posted - 09/18/2014 :  05:20:28  Show Profile  Reply with Quote
Use below Sp which will help to fix the issue ...DBNAME = ur own database name

=========================
USE master
GO
EXEC sp_resetstatus 'DBNAME'
GO
ALTER DATABASE DBNAME SET EMERGENCY
GO
DBCC CHECKDB('DBNAME')
GO
ALTER DATABASE DBNAME SET SINGLE_USER WITH

ROLLBACK IMMEDIATE
GO
DBCC CHECKDB('DBNAME',REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS

GO
ALTER DATABASE DBNAME SET MULTI_USER

GO
DBCC CHECKDB('DBNAME')
GO





Bishu
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000