MSSQL 2000 (MSDE 8.0.760) Error 926 after power-failure crash

Question

Hello to all,

We are using an MSDE 8.0.760 for an application called "Verity Teleform". Yesterday the server crashed after a power- and additional UPS-failure. Today the application reported an OLE-DB Error "E_Fail". Application Error-Log reported 3 MSSQL-Errors (see below). I did the following steps:

I installed the MSSMSE and checked the DB. The DB-Server is runnig but one dbase is marked as suspect. I suppose this is the reason for the OLE-DB Error. I now tried DBCC CHECKDB on the suspect database but Error 926 occures preventing the DB from beeing opened.

Is there a way I can repair this Database? I have read something about an emergency mode but was not able to find a way to put the database into this mode. I should mention, that I learned today that there is no backup of this database at all and that its vital to get this db working again. I hope someone will be able to help me, I am starting to get desperate.

Thank you all in advance

Yours Frank

Application-Error-Log (sorry partly in german):

Ereignistyp: Fehler
Ereignisquelle: MSSQLSERVER
Ereigniskategorie: (2)
Ereigniskennung: 17052
Datum: 24.04.2012
Zeit: 13:46:46
Benutzer: Nicht zutreffend
Computer: KIBALI
Beschreibung:
Fehler: 9003, Schweregrad: 20, Status: 1
The LSN (1991903:42:2) passed to log scan in database 'sp_defaultdb' is invalid.

Ereignistyp: Fehler
Ereignisquelle: MSSQLSERVER
Ereigniskategorie: (2)
Ereigniskennung: 17052
Datum: 24.04.2012
Zeit: 13:46:46
Benutzer: Nicht zutreffend
Computer: KIBALI
Beschreibung:
Fehler: 3313, Schweregrad: 21, Status: 2
Error while redoing logged operation in database 'sp_defaultdb'. Error at log record ID (1991903:42:2).

Ereignistyp: Fehler
Ereignisquelle: MSSQLSERVER
Ereigniskategorie: (2)
Ereigniskennung: 17052
Datum: 24.04.2012
Zeit: 13:46:46
Benutzer: Nicht zutreffend
Computer: KIBALI
Beschreibung:
Fehler: 3414, Schweregrad: 21, Status: 1
Database 'sp_defaultdb' (database ID 5) could not recover. Contact Technical Support.

Answer 1

Hi,

Please refer this article for the best practices when dealing with a suspect database.

---

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @PradeepAdiga

Answer 2

Thanx alot even though it was not as easy because MSDE 2000 lacks some of the commands you use in your article. Here is what I did, maybe it helpes someone else here:

Having a suspect database you first have to put it in emergency mode to be able to access it again. Unfortunatelly in MSDE 2000 this cannot be done in one single command. Here is what to do:

use [master]
go
execute sp_configure 'allow updates',1
reconfigure with override
go 
update sysdatabases 
set status = 32768 
where name = '<database name>'
go

Then you have to shutdown and restart the SQL-Server. The suspect Database is now in Emergency-Mode and can be accessed again. To do repair-work on it you additionally have to put it in single_user mode. I did this by using the MSSMSE by opening the properties of the emergency database. In the "options" Tab you find the "restrict access" option, which you have to set to "single_user". Again you have to restart the SQL Server then you can use DBCC CHECKDB with all repair options on the DB.

When done you have to put the DB back into normal mode. Again you have to do it in a rather complicated way:

use [master]
go
update sysdatabases 
set status = 0 
where name = '<database name>'
go
execute sp_configure 'allow updates',0
reconfigure with override

Restart the server again and now the DB might be in normal state again, at least it was here.

I have to mention that all this could damage your database beyond the possibility of repair. So this is the final option to choose if you dont have a backup to use. Also the integrity of the data might be harmed after repairing. So carefully check your data after this procedure.

Thank you all for helping me

Yours Frank