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.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Database in Emergency Mode (bypass recovery)

Author  Topic 

petemill66
Starting Member

1 Post

Posted - 2007-11-21 : 11:47:26
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

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-21 : 16:30:13
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

1 Post

Posted - 2014-09-18 : 05:20:28
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
   

- Advertisement -