- 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.