MSSql & WSUS: Database cannot be opened. Marked as suspected

Hi,

the WSUS DB (MSSQL Express 2005) of a customer won’t start after a hardware failure and bluescreen. Here are the steps which recovers the database back in an usable state.

The SQL Server Instance logs errors like this

"Could not redo log record (183366:6166:54), for transaction ID (0:117029942), on page (1:657511), database 'SUSDB' (database ID 5). Page: LSN = (183267:2976:260), type = 2. Log: OpCode = 2, context 3, PrevPageLSN: (183366:1717:287). Restore from a backup of the database, or repair the database."

in “C:\Windows\SYSMSI\SSEE\MSSQL.2005\MSSQL\LOG\ERRORLOG” and the database is down.

First connect to the DB Instance using a named pipe

c:\> sqlcmd -S np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

and see state of the Database. Replace SUSDB with the Databasename of your DB. Try to repair the Database.

The repair attempt fails…

DBCC CHECKDB ('SUSDB') WITH NO_INFOMSGS, ALL_ERRORMSGS;
go
Error MEssage: Database ‘SUSDB’ cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.

Check state of the Database

SELECT state_desc,name FROM sys.databases where name='SUSDB'
go
state_desc                       name
----------------------------------------
SUSPECT                          SUSDB

Ooohhh Suspected, reset the state

sp_resetstatus 'SUSDB'
go

Set Emergency Mode

ALTER DATABASE SUSDB SET EMERGENCY
go

Check DB without repair

DBCC CHECKDB (SUSDB)
go
There are 2501426 rows in 13055 pages for object "tbPrerequisiteDependency".
CHECKDB found 0 allocation errors and 21 consistency errors in table 'tbPrerequisiteDependency' (object ID 2137058649).
CHECKDB found 26 allocation errors and 1120 consistency errors in database 'SUSDB'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (SUSDB).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Switch to single User mode

ALTER DATABASE SUSDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go

and repair the Database

DBCC CHECKDB('SUSDB',REPAIR_ALLOW_DATA_LOSS)
go
The error has been repaired.
There are 2501426 rows in 13055 pages for object "tbPrerequisiteDependency".
CHECKDB found 0 allocation errors and 21 consistency errors in table 'tbPrerequisiteDependency' (object ID 2137058649).
CHECKDB fixed 0 allocation errors and 21 consistency errors in table 'tbPrerequisiteDependency' (object ID 2137058649).
CHECKDB found 26 allocation errors and 1124 consistency errors in database 'SUSDB'.
CHECKDB fixed 26 allocation errors and 1124 consistency errors in database 'SUSDB'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Switch to multi user mode

ALTER DATABASE SUSDB SET MULTI_USER
go

And get back online

ALTER DATABASE SUSDB  SET ONLINE
go