How To Repair A Suspect Database In MSSQL
- 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.
Steps to fix:
- Open Microsoft SQL Server Management Studio and connect to your database
- Click the New Query button
- Paste the following SQL script into your New Query page replacing [YourDatabase] with the name of your database.
PgSQL123456EXEC sp_resetstatus [YourDatabase];ALTER DATABASE [YourDatabase] SET EMERGENCYDBCC checkdb([YourDatabase])ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATEDBCC CheckDB ([YourDatabase], REPAIR_ALLOW_DATA_LOSS)ALTER DATABASE [YourDatabase] SET MULTI_USER
- Click Execute
Your database should no longer be tagged as (suspect) and you should be able to access it.