Menu Close

Suspect SQL Database

Here is how to fix a SUSPECT database in SQL 2005

(Try this first)
EXEC sp_resetstatus 'DBName'
GO

 

(This will repair it if the resetstatus is unsuccessful)

ALTER DATABASE DBName SET EMERGENCY

DBCC checkdb('DBname')

ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ('DBName', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE DBName SET MULTI_USER

ALTER DATABASE DBName SET ONLINE

 

GO

 

********************************************************************

 

How to Recreate a msdb database that has been marked suspect.

 

•Start SQL Server with trace flag 3608 (to allow you to detach msdb). I did this by shutting down SQL Server and then going to the 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn' directory and from a command prompt doing 'start sqlservr.exe -c -T3608'

•Use the master database and detach msdb using "sp_detach_db 'msdb'"

•Rename the damaged msdb files (msdbdata.mdf and msdblog.mdf in the 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data' directory)

•Run the instmsdb.sql script from the 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install' directory

•Shutdown and restart SQL Server normally