Unable to set ShareWebDb back to MULTI

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Unable to set ShareWebDb back to MULTI_USER...

Unable to set ShareWebDb back to MULTI_USER and ONLINE

Rate Topic

Display Mode

Topic Options

Author

Message

mjjordan

Posted Wednesday, January 23, 2013 7:46 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 8:36 AM
Points: 4, Visits: 15

- 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

anthony.green

Posted Wednesday, January 23, 2013 7:49 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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 »

Permissions