Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Unable to set ShareWebDb back to MULTI_USER and ONLINE Expand / Collapse
Author
Message
Posted Wednesday, January 23, 2013 7:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 17, 2013 6:16 AM
Points: 4, Visits: 16
- 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.
Post #1410590
Posted Wednesday, January 23, 2013 7:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:53 AM
Points: 5,132, Visits: 4,922
Restore from backup would be the only way now I am guessing due to repair_allow_data_loss being run and terminated.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1410594
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse