locked
database in emergency mode RRS feed

  • Question

  • Dear All,

    My database was in emergency mode so i need to bring it back to normal mode. How to bring the database back from emergency mode my database size is 350 gb.

    Please help.

    Prabeen kumar patra

    Micromax informatics ltd.

    Wednesday, June 12, 2013 1:53 AM

Answers

  • Hi Parbeen,

    Please read the below link. It will give you the detailed explanation about how to do it.

    http://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-emergency-mode-repair-the-very-very-last-resort/

    Thanks and Regards,

    Shanoof Basheer

    -------------

    Please Mark as Answer/Propose as Answer or Vote as Helpful if it serves the purpose.

    • Proposed as answer by Fanny Liu Tuesday, June 18, 2013 10:00 AM
    • Marked as answer by Fanny Liu Thursday, June 20, 2013 8:01 AM
    Wednesday, June 12, 2013 2:53 AM
  • Hi Mr. Prabeen

    First need to run DBCC on your SQL Server for particular database. Below is the command :

    "DBCC checkdb('dbname') "

    The above gives the healthy status of HDD, If there is any HDD failures observed on Server, need to go for moving ldf & mdf files from on location to another location.

    If the HDD is fine there wont be any damage to .mdf & .ldf files. Then need to change the DB mode to single user mode. Please use below command .

    "ALTER DATABASE dbname, SET SINGLE_USER WITH ROLLBACK IMMEDIATE "

    Here you must agree for little bit of data loss in this process, if it is OK , then need to proceed or else simply  restore the recent full back-up.

    *** If agrees for little bit data loss then reun below command :

    "BCC CheckDB ('dbname', REPAIR_ALLOW_DATA_LOSS) "

    *** Then need to bring back the database to Normal multi-user mode by executing below query.

    "ALTER DATABASE movies SET MULTI_USER"

    • Proposed as answer by Fanny Liu Tuesday, June 18, 2013 10:00 AM
    • Marked as answer by Fanny Liu Thursday, June 20, 2013 8:01 AM
    Friday, June 14, 2013 7:55 AM

All replies

  • Hi Parbeen,

    Please read the below link. It will give you the detailed explanation about how to do it.

    http://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-emergency-mode-repair-the-very-very-last-resort/

    Thanks and Regards,

    Shanoof Basheer

    -------------

    Please Mark as Answer/Propose as Answer or Vote as Helpful if it serves the purpose.

    • Proposed as answer by Fanny Liu Tuesday, June 18, 2013 10:00 AM
    • Marked as answer by Fanny Liu Thursday, June 20, 2013 8:01 AM
    Wednesday, June 12, 2013 2:53 AM
  • Alter Database YourDbname Set Multi_user


    Srinivasan

    Wednesday, June 12, 2013 3:35 AM
  • Do you have last good backup?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Wednesday, June 12, 2013 5:17 AM
  • Hi Mr. Prabeen

    First need to run DBCC on your SQL Server for particular database. Below is the command :

    "DBCC checkdb('dbname') "

    The above gives the healthy status of HDD, If there is any HDD failures observed on Server, need to go for moving ldf & mdf files from on location to another location.

    If the HDD is fine there wont be any damage to .mdf & .ldf files. Then need to change the DB mode to single user mode. Please use below command .

    "ALTER DATABASE dbname, SET SINGLE_USER WITH ROLLBACK IMMEDIATE "

    Here you must agree for little bit of data loss in this process, if it is OK , then need to proceed or else simply  restore the recent full back-up.

    *** If agrees for little bit data loss then reun below command :

    "BCC CheckDB ('dbname', REPAIR_ALLOW_DATA_LOSS) "

    *** Then need to bring back the database to Normal multi-user mode by executing below query.

    "ALTER DATABASE movies SET MULTI_USER"

    • Proposed as answer by Fanny Liu Tuesday, June 18, 2013 10:00 AM
    • Marked as answer by Fanny Liu Thursday, June 20, 2013 8:01 AM
    Friday, June 14, 2013 7:55 AM
  • Run first

    ALTER DATABASE 'YourDBName' SET MULTI_USER

    If throw error then go for below:

    ALTER DATABASE 'YourDBName' SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DBCC CHECKDB ('YourDBName', REPAIR_ALLOW_DATA_LOSS)
    ALTER DATABASE 'YourDBName' SET MULTI_USER

    Friday, June 14, 2013 2:57 PM