SQL 2005: Database recovery from suspect db

[Stevehewitt]

Hi all,

Not a SQL DBA, so please bare with me!

Got SharePoint 2007, which uses SQL 2005 on the locahost.

For some unknown reason, the searching service in SharePoint wouldn't work, and after a bit of troubleshooting it appears that the 'SharedServices1_Search_DB' database is flagged as suspect. I have no idea why this is, as I am unaware of any changes to the system prior to it working fine.

We've only have SharePoint 2 months, and thus still waiting for a backup exec licence for the sharepoint agent - so no backup!!!!

So far, i've tried the FAQ on this forum, and ran the following statements:

USE master
        GO
        sp_configure 'allow updates', 0
        GO
        RECONFIGURE WITH OVERRIDE
        GO 

sp_resetstatus 'SharedServices1_Search_DB'

This simply resulted in "The suspect flag on the database "SharedServices1_Search_DB" is already reset."

I've also ran:

Use Master
Go
Dbcc Checkdb('SharedServices1_Search_DB')

Which just tells me that the DB cannot be checked as it's already suspect. (how useful!)

It's a SQL 2005 Enterprise Edition Installation with Service Pack 2 and all recent hotfixes etc. There's multiple databases in this instance, all of which are working fine. There is also another instance which is used for another application.

If someone could help it would be very much appreciated!!!

Cheers,




Steve.

"They have the internet on computers now!" - Homer Simpson

 

[Jamfool]

Check the SQL Server Logs (Under the Management Tab) to see why this databases is being marked as suspect.

 

[onpnt]

Check to see if the log file is missing. I recall a trick to fix this is dettaching the db and reattaching it. This will create the log file if not found. However you need to put the db in emergency mode or it will not let you dettach it


[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[mrdenny]

Before you detach it (because if that isn't the problem you won't be able to attach it) run dbcc checkdb on it.

alter database SharedServices1_Search_DB
set EMERGENCY 
GO
DBCC CHECKDB ('SharedServices1_Search_DB')
GO

See what that tells you is wrong with the database.

If you don't see anything in the log that you reconigize feel free to post it here and we can take a look about it.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[bjb123]

- so no backup!!!!

Those words are painful to read even when it isn't my problem.

 

[Stevehewitt]

Hi guys,

Thank you all for your help and advice - much appreciated.

So far no luck. I haven't tried emergency mode, as I believe that will take down all SQL connections and it's a production MOSS box (so need to do it out of hours)

However, emergency mode aside I can detach the suspect DB without any problems, and I also when I try to run CHECKDB it just errors out saying that it's suspect so nothing can be done!

I'll give it another go in Emergency mode, but I've also tried the remove the LDF and try to reattach trick but that just failed too!

Cheers,




Steve.

"They have the internet on computers now!" - Homer Simpson