DBCC CHECKDB question

Apologies if this is in the wrong section....That said...

We had an extended power outtage last week that took a MS SQL server offline, and while it was on a UPS that shut it down, we had some problems with the DB afterwards that may have also been related to the disk getting so full that a maintenance backup/etc. couldn't run. The database is now marked suspect.

I'd like to repair it if possible, and after some research I came across the DBCC CHECKDB procedure. I ran:

DBCC CHECKDB
( sgmsdb , REPAIR_REBUILD )
WITH ALL_ERRORMSGS

and received

Server: Msg 926, Level 10, State 1, Line 1
Database 'sgmsdb' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.

It sounded like this procedure was one that might help with suspect databases. Does that mean that the DB is beyond repair with this tool, and is there another procedure other than restoring that I might use to repair a database?

Sine you cannot access your db currrently as it is marked suspect, try to bring it normal as follows,

1.execute this ssms sp_resetstatus 'dbname' and after that you need to restart sql services........if this method fails,

2.try to detach the db and then again attach it it should come online but if you cannot attach the db after detaching it try as follows,

(a) create a database with the same name in another directory as the one you're trying to attach
(b) re-create all filesgroups and files as necessary
(c) shutdown the server
(d) swap in the old mdf file and any ndf files
(e) bring up the server and let the database attempt to be recovered and then go into suspect mode
(f) put the database in single_user and emergency modes
(g) run DBCC CHECKDB (dbname, REPAIR_ALLOW_DATA_LOSS) which will rebuild the log and run full repair
(h) return database to online, multi_user mode

3.if you fail in steps 1 and 2 you need to put the db into emergency mode

ALTER DATABASE DBNAME SET EMERGENCY and after that you can export the datas to another db using import export option..........

|||

These resource may help:

Corrupt or Suspect Database –Recommended Actions
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
http://www.myitforum.com/articles/18/view.asp?id=7381

|||

Refer these links and possitively post back if u could solve the issue. I have seen lot of solutions to this suspect database issue. But never seen a consistant one.

Refer

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1648300&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1902831&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1225944&SiteID=1

Madhu