RSS

How To Repair A Suspect Database In MSSQL Server Database

24 Feb

How To Repair A Suspect Database In MS SQL

Issue
You have a database in MS SQL that is tagged as (suspect) 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.  If you do not have this installed please install Microsoft SQL server Management Studio to connect database server.

 Open Microsoft SQL Server Management Studio and connect to your database

  1. Click the New Query button
  2. 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
  3. Click Execute

Your database should no longer be tagged as (suspect) and you should be able to access it.

Please refer this: https://support.powerdnn.com/kb/a398/how-to-repair-a-suspect-database-in-mssql

 

Advertisements
 
Leave a comment

Posted by on February 24, 2014 in SQL Server

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

 
%d bloggers like this: