Post reply

Unable to set ShareWebDb back to MULTI_USER and ONLINE

  • January 23, 2013 at 7:46 am

    #269714

    - We had an issue with ShareWebDb where we lost access to companyweb because the 'user' was unable to login. I checked the status of the database and it was in SUSPECT status.

    - As a result of my lack of experience with MS SQL I searched for a solution to this and after attempting several methods found with no success (setting Auto Close etc...) I ended up running the following that I found on a blog about fixing the lack of access to the companyweb;

    -- CHECK THE STATUS OF THE DATABASE

    -- SELECT DATABASEPROPERTYEX('ShareWebDb', 'STATUS') as 'DBStatus'

    -- IF DATABASE IS 'SUSPECT' set to 'emergency'

    -- ALTER DATABASE ShareWebDb SET EMERGENCY

    -- Set database to single user mode to run checkdb utility

    -- ALTER DATABASE ShareWebDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    -- run the checkdb command

    -- DBCC CHECKDB (ShareWebDb, REPAIR_ALLOW_DATA_LOSS)

    -- Set database back to Multi User mode

    -- ALTER DATABASE ShareWebDb SET MULTI_USER WITH ROLLBACK IMMEDIATE

    I 'NOW' know that running DBCC CHECKDB (ShareWebDb, REPAIR_ALLOW_DATA_LOSS) was a huge newbie mistake.

    After having it run for 4 days on a 500MB database i 'cancelled' it.

    This then took another two days of backing out and did not complete before I lost patience and checked the status and found it was suspended. I therefore rebooted the server last night.

    Now when I try and access the ShareWebDb that is still in SINGLE USER MODE and EMERGENCY status I get an error stating that it is already open in SINGLE USER MODE and cannot be accessed.

    I did some digging and determined the SPID of the process but the 'cmd' of this process is "AWAITING COMMAND" and therefore, based on some more looking around and a now extreme sense of caution, I know that using a 'kill' on this spid could/will result in me digging a deeper hole for myself.

    Because this process has taken so many days my last backup is now several days old so I would prefer to bring the existing database back online of at all possible.

    Does anyone have any suggestions as to how I can get this database back online?

    Thanks in advance for your time and patience.

  • January 23, 2013 at 7:49 am

    #1580101

    Restore from backup would be the only way now I am guessing due to repair_allow_data_loss being run and terminated.

Viewing 2 posts - 1 through 1 (of 1 total)

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