Skip to Content

You may face an issue occasionally which will hamper your work and keep you tensed.

I can name this issue as ‘Suspect DB’.When the DB is marked as ‘Suspect’,then you cannot do anything with  that database.

There are some possible reasons which mark the DB as Suspect,they are

1. Database could have been corrupted.

2. There is not enough space available for the SQL Server to recover the database during startup.

3. Database cannot be opened due to inaccessible files or insufficient memory or disk space.

4. Database files are being held by operating system, third party backup software etc.

5. Unexpected SQL Server Shutdown, Power failure or a Hardware failure.

You can get rid out of the suspect by applying the below solutions.

The best solution to recover suspect DB is :You can restore the latest backup set,but, what will happen when you dont have it?

Dont be panic!!!..Try my solution which should be chosen as Last option,when you are cornered

There are some pre-requisites which you need to abide before proceeding my steps:

1.This is only for Microsoft SQL Server

2.This may loss your data,while you are repairing with Allow data loss.

3.These statements will hit your DB directly,so.consult with SAP before proceeding this.



There are some steps which require to bring the suspected DB to Normal.

Step 1:Make the DB to work in Emergency mode which will block write  access to it.

        To bring it to emergency,the execute

       ALTER DATABASE DBNAME SET EMERGENCY

        GO

step 2:Run the another command to check for consistency errors and try  to repair with ‘Repair’ Command.

        ALTER DATABASE <DBNAME> SET SINGLE_USER

        GO

        DBCC CHECKDB (‘DBNAME’, REPAIR_ALLOW_DATA_LOSS)

        GO

Step 3:If You find any consistency errors,then execute

        USE <Your DB Name>

        GO

        — Determine the original database status

        SELECT [Name], DBID, Status

        FROM master.dbo.sysdatabases

        GO

        — Enable system changes

sp_configure ‘allow updates’,1

        GO

        RECONFIGURE WITH OVERRIDE

        GO

        — Update the database status

        UPDATE master.dbo.sysdatabases

        SET Status = 24

        WHERE [Name] = ‘YourDatabaseName’

        GO

        — Disable system changes

sp_configure ‘allow updates’,0

        GO

        RECONFIGURE WITH OVERRIDE

        GO

        — Determine the final database status

        SELECT [Name], DBID, Status

        FROM master.dbo.sysdatabases

        GO

Step 4:After executing the above command,then run the below command  again,

        ALTER DATABASE <DBNAME> SET SINGLE_USER

        GO

        DBCC CHECKDB (‘DBNAME’, REPAIR_ALLOW_DATA_LOSS)

        GO

Step 5:When you find 0 consistency errors,you have to bring the DB from  Emergency mode to normal.

        ALTER DATABASE <DBNAME> SET MULTI_USER

        GO

Refresh and check.

Revert me for any clarification.

To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

  1. Chris Kernaghan

    Kamba,

    This could be a very useful blog, but I fear it suffers from a lack of explanation of certain things.

    1.You should clarify that this is for SQL Server

    2. You should make the statement that this procedure is very dangerous, and there are certain things you should do before attempting – like copying all the SQL Server datafiles to ensure you have a roll back point

    3. Confirm to the readers that it is likely that they will lose data, and there is no guarantee where that data will be lost from and that the system may not actually be supportable by SAP in the future

    4. Also make the statement that this should only be undertaken in consultation with SAP, as it may result in a loss of support

    5. Explain of provide links to the SQL Server pages that explain what ‘Suspect’ is and what the implications are, as well as some of the commands you are asking people to run and to implicitly trust your method – when you and they have a distinct lack of information about the current situation the user finds themselves.

    This blog has potential, and I hope that you will continue to provide information to the community as I like your other contributions

    Thanks

    Chris

    (0) 
      1. Chris Kernaghan

        Kamba,

        I am very glad you did not take my comments the wrong way and also glad to see that your blog post is now a better resource as it more fully explains things and can provide a better starting point for people in this situation 🙂

        Chris

        (0) 
  2. John Walker

    You should backup your database after changing to single user mode. This is just to make sure that when we run DBCC with data loss option, we will have the backup available also there is a 3rd party SQL database repair tool that successfully repairs & recovers data from suspect database

    (0) 

Leave a Reply