in MySQL

How To Repair A Suspect Database In MSSQL

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Issue

You have a database in MS SQL that is tagged as (Suspect Database) and you are unable to connect to the database.

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.

Resolution

These steps require you to have Microsoft SQL Server Management Studio installed on your computer.

  • 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.

EXEC sp_resetstatus [YourDatabase];
ALTER DATABASE [YourDatabase] SET EMERGENCY
DBCC checkdb([YourDatabase])
ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC 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.

Related Posts

Add a Comment

Your email address will not be published. Required fields are marked *