Post reply

change back the db from emergency to normal

  • July 12, 2009 at 4:52 am

    #136503

    Hello Experts,

    I executed the below query and found that the DB moved to "Emergency state"

    ALTER DATABASE abc SET EMERGENCY

    How to change it back ot normal?

    Regards

    Sourav

    Thanks.

  • July 12, 2009 at 5:00 am

    #1023589

    Sourav Mukherjee (7/12/2009)

    How to change it back ot normal?

    ALTER DATABASE abc SET ONLINE


    Pradeep Singh

  • July 12, 2009 at 5:13 am

    #1023591

    Thanks Pradeep...I forgot the command..:-)

    By the way, I was in a mood to do some R&d. So deleted the .LDF from one of the database. (@home system..Chill!!).

    Now unable to do any activity with the database.

    If I have a backup, then I can restore the database.. And the issue will be resolved. That is fine.

    But I do not have any bkp here.

    Is there any other way to fix the issue?

    note: While checking found that dbcc rebuild_log() doesn't work on sql server 2008.

    I am using 2008 version.

    Can you please suggest?

    Regards

    Sourav

    Thanks.

  • July 12, 2009 at 5:15 am

    #1023592

    I suggest you to go through THIS article by Gail. Very good explaination on what options we have when log gets full or missing.

    Edit-fixed the URL


    Pradeep Singh

  • July 12, 2009 at 5:39 am

    #1023594

    Hello Pardeep

    I performed the following steps:

    1. detached the db.

    2. executed the below query to create a new db considering the earlier available .mdf file(abc.mdf), but mentioned a new .ldf file name.

    CREATE DATABASE abc1

    ON (NAME = abc_dat,

    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\abc.mdf',

    SIZE = 10,

    MAXSIZE = UNLIMITED,

    FILEGROWTH = 5 )

    LOG ON ( NAME = abc_log,

    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\abc1_log.ldf',

    SIZE = 5MB,

    MAXSIZE = 5MB);

    GO

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

    Getting the below error

    Msg 5170, Level 16, State 1, Line 1

    Cannot create file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\abc.mdf' because it already exists.

    Change the file path or the file name, and retry the operation.

    Msg 1802, Level 16, State 4, Line 1

    CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

    Thanks.

  • July 12, 2009 at 5:52 am

    #1023601

    While creating a new database, you've specified the path of the new data/log files where files with the names u've specified already exist. Either change the path or change the file name.


    Pradeep Singh

  • July 12, 2009 at 5:58 am

    #1023602

    yes,

    1. I created the new db as follows::

    CREATE DATABASE abc1

    ON (NAME = abc_dat,

    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\abc1.mdf',

    SIZE = 10,

    MAXSIZE = UNLIMITED,

    FILEGROWTH = 5 )

    LOG ON ( NAME = abc_log,

    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\abc1_log.ldf',

    SIZE = 5MB,

    MAXSIZE = 5MB);

    GO

    2. Then stopped the SQL Server.

    3. Deleted the 2 files(mdf and ldfs).

    4. Then changed the .mdf file of the old file(abc.mdf) to abc1.mdf

    5. Resatrted the SQL Service

    6. Still no luck

    "Msg 945, Level 14, State 2, Line 1

    Database 'abc1' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

    "

    Thanks.

  • July 12, 2009 at 6:01 am

    #1023604

    dbcc checkdb

    DBCC results for 'sys.filestream_tombstone_2073058421'.

    There are 0 rows in 0 pages for object "sys.filestream_tombstone_2073058421".

    DBCC results for 'sys.syscommittab'.

    There are 0 rows in 0 pages for object "sys.syscommittab".

    CHECKDB found 0 allocation errors and 0 consistency errors in database 'mssqlsystemresource'.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Thanks.

  • July 12, 2009 at 9:08 am

    #1023618

    Sourav Mukherjee (7/12/2009)

    2. Then stopped the SQL Server.

    3. Deleted the 2 files(mdf and ldfs).

    4. Then changed the .mdf file of the old file(abc.mdf) to abc1.mdf

    5. Resatrted the SQL Service

    ALTER DATABASE < DB name > SET EMERGENCY

    GO

    DBCC CheckDB('< DB name >, REPAIR_ALLOW_DATA_LOSS)

    Now try and use the DB. Note that this can very well cause data loss and leave you with a transactionally inconsistent database.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 13, 2009 at 12:12 am

    #1023680

    Thanks Gail..It works now.I verified the same. 😛

    Just before running the dbcc checkdb statement I had to take it in single_user mode.

    Have a great day to you!

    Regards

    Sourav

    Thanks.

  • July 13, 2009 at 2:08 am

    #1023714

    Sourav Mukherjee (7/13/2009)

    Just before running the dbcc checkdb statement I had to take it in single_user mode.

    Yup. All repairs have to be run with the DB in single user mode.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply