:::: MENU ::::

Repair SQL Server 2008 R2 Suspected Database

Just a day before , come across new issue of database . for some reason database found in suspect mode ! and that was horrible moment.

After some search , we got brief idea that , database can go in suspect mode for many reasons like improper shutdown of the database server, corruption of the database files etc.

To find out what exactly gone wrong with database we tried following query

DBCC CHECKDB (‘YOUR_DATABASE_NAME’) WITH NO_INFOMSGS, ALL_ERRORMSGS

(It wasn’t much helpful in my case . I am just posting here in-case it hepls other !)

After a while we run another block of code , to put the database in emergency mode (Ref  : MSDN )

EXEC sp_resetstatus ‘YOUR_DATABASE_NAME’;
ALTER DATABASE YOUR_DATABASE_NAME  SET EMERGENCY
DBCC checkdb(‘YOUR_DATABASE_NAME’)
ALTER DATABASE YOUR_DATABASE_NAME  SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (‘YOUR_DABASE_NAME’, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE YOUR_DATABASE_NAME SET MULTI_USER

Finally we got database back online !

Happy Coding .


43 Comments

  • Reply Prett Sons |

    Hello Vinay,

    Good to see your article on SQL database issues & you have written it very nicely!! I am Adam & I also love to write article on database issues.

  • Reply Frances |

    Thank you for sharing this information! I greatly appreciate that you took the time to write this and share.

    I am not a DBA and I really needed help.

  • Reply Anonymous |

    This is an excellent post. This is all I needed to fix the issue.
    Thanks a lot.

  • Reply Arshad |

    Thanks Vinay.

    Really it helps me to get my database back which was in suspect mode. I google everywhere that helps me but here i found out solution.

    Thanks once again.

    Arshad

  • Reply Shuja |

    Dear

    Thank you for your help can you please let me now that how much time it will take to resolve my database from suspect to normal way?

    • Reply Vinay Patel |

      Hi ,

      I am not sure what parameters are affecting in recovery from suspected mode – in my scenario – it was 6 GB database and took about 20 minutes.

      Thanks
      Vinay

      • Reply Shuja |

        Actually Vinay my query till executing from 24 hours now can you please suggest
        me what should be my next step?

          • Shuja |

            My DB size is 24GB and i have applied this query
            EXEC sp_resetstatus ‘ServiceManager’;

            ALTER DATABASE ServiceManager SET EMERGENCY

            DBCC checkdb(‘ServiceManager’)

            ALTER DATABASE ServiceManager SET SINGLE_USER WITH ROLLBACK IMMEDIATE

            DBCC CheckDB (‘ServiceManager’, REPAIR_ALLOW_DATA_LOSS)

            ALTER DATABASE ServiceManager SET MULTI_USER

            and now i am at this one
            DBCC CheckDB (‘ServiceManager’, REPAIR_ALLOW_DATA_LOSS)

            above 4 queries has been completed now i am at DBCC’s query.

          • Vinay Patel |

            Things are going right way – it seems your log is not committed and there are must be non- committed running transaction

            Your db is ready – just let it finish DBcheck to make sure after recovery data integrity is perfect !

  • Reply DJ |

    Thanks so much,

    I was even scared with the allow_data_loss
    But was surprised when I got my data.
    Thanks Mr Patel

  • Reply Dan K |

    This post has bailed me out twice in the past week now. Thanks very much!
    Any ideas why a db might enter “suspect” mode in the first place?

  • Reply Jerry Pyne |

    Thanks for the approach and comments but in the spirit of sharing I had a different problem that was simply solved by recycling the server.
    The problem was my fault because it was caused by running out of disk space.
    Disk space was added (by deleting old backups) but the Suspect did not get cleared using the above commands… Because of an unrelated problem the First alter failed (by the way you should enter the first two commands and get the results BEFORE executing the third command).

    Because of an unrelated problem and the first three commands ran forever (and there was NO disk or CPU activity). The stop button uncovered the failed alter. The “unrelated problem was associated with our tempdb growth and time outs in attempts to read the database.

    Restarting the SQL server fixed both problems but I would only do that if you get time outs on the tempdb and there is no I/O activity on the database.

    Good luck.

    • Reply Vinay Patel |

      Thanks for feedback !

      Actually , I tried to draft only the scenario what I have come across ! , And definitely will make sure to cover up all other aspects too

      Thanks
      Vinay

  • Reply Terry |

    I think it would be wise to point out that the first script needs to be ran first, to assess the amount of dataloss you will incur before doing the repair. Repairing should be the very last option, as not knowing what you will lose before repairing the database can make for some very difficult future problems to track down.

So, what do you think ?