How To Repair A Suspect Database In MSSQL
Possible Causes
The database could have become corrupted.
There is not enough space available for the SQL Server to recover the database during startup.
The database cannot be opened due to inaccessible files or insufficient memory or disk space.
The database files are being held by operating system, third party backup software etc.
There was an unexpected SQL Server Shutdown, power failure or a hardware failure.
Solution:
EXEC SP_resetstatus databaseName
ALTER DATABASE databaseName SET EMERGENCY
DBCC checkdb databaseName
ALTER DATABASE databaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CHECKDB (databaseName, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE databaseName SET MULTI_USER
Possible Causes
The database could have become corrupted.
There is not enough space available for the SQL Server to recover the database during startup.
The database cannot be opened due to inaccessible files or insufficient memory or disk space.
The database files are being held by operating system, third party backup software etc.
There was an unexpected SQL Server Shutdown, power failure or a hardware failure.
Solution:
EXEC SP_resetstatus databaseName
ALTER DATABASE databaseName SET EMERGENCY
DBCC checkdb databaseName
ALTER DATABASE databaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CHECKDB (databaseName, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE databaseName SET MULTI_USER
