Repair Suspect Database, Steps to Repair Suspect Database in SQL Server
Related Topics: Fix Database Corruption in SQL Server>
Next Topic: FIND & FIX SUSPECT PAGES
There can be a possibility that when you connect to an SQL Server Instance you will find the database being marked as SUSPECT. During such scenarios, you will not be able to connect to the database. In this article we will go through the steps which you need to follow to recovery a database which is marked as SUSPECT. The steps mentioned in this article works on SQL Server 2005 and higher versions.
This article has a total of 2 Pages including this page.
Follow the links below to open both the pages in a separate
window so that you can easily follow the steps to Repair Suspect
Database in SQL Server
Page 1 & Page 2.
Show us your support by Liking us on Facebook and by Subscribing to our Weekly News Letter so that you are better prepared for the challenges of tomorrow. Don't forget to check out free White Papers to grow your technical knowledge.
Some of the reasons why an SQL Server database can be marked as SUSPECT
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.
Due to a hardware failure one of our database namely BPO was marked SUSPECT when the SQL Server came back online. Already due to the hardware failure we had downtime for more than two hours and adding to that when the server came back online our mostly critical database was marked as SUSPECT.
SQL Server Disaster Recovery Tips
• What are Virtual Log Files in SQL Server Transaction Log File?
• How to Use Dedicated Administrator Connection in SQL Server
• How to Start SQL Server without TempDB Database?
• Different Startup Options for SQL Server Database Engine Service
• How to Detect Corruption Issues in SQL Server Using Suspect_Pages Table?
• How to Fix SQL Server Database Corruption Issues?
• Steps to Repair a Suspect Database in SQL Server?
• Different Ways to Find Default Trace Location in SQL Server
Steps to Recover Database Marked as SUSPECT
1. Execute the below mentioned TSQL code to identify all the databases which are marked as SUSPECT.
USE master GO SELECT NAME,STATE_DESC FROM SYS.DATABASES WHERE STATE_DESC='SUSPECT' GO
2. Open the latest SQL Server Error Log and check for errors logged for the database which is marked as suspect. You can open SQL Server Error Log by expanding Management Node -> SQL Server Error Logs. In my server I could find below mentioned entries in SQL Server Error Logs.
Sample Error Messages within SQL Server Error Log when database is marked as SUSPECT
Starting up database 'BPO'. Error: 9003, Severity: 20, State: 9. The log scan number (189624:16:2) passed to log scan in database 'BPO' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup. Error: 3414, Severity: 21, State: 1. An error occurred during recovery, preventing the database 'BPO' (database ID 10) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support. CHECKDB for database 'BPO' finished without errors on 2009-12-15 11:30:28.320 (local time). This is an informational message only; no user action is required.
Recommendation: You can't fix an SQL Server Performance Issues until you can identify it. Download Fully Functional Database Performance Analyzer and see how it will pinpoint SQL Server Performance Issue! Become the STAR Performer!
3. When a database is in SUSPECT mode you will not be able to get connected to the database. Hence you need to bring the database first in EMERGENCY mode to repair the database. Execute the below mentioned TSQL code to bring the database in EMERGENCY mode.
Important: Following set of Articles can help you avoid Database Corruption Issues in SQL Server to a great extend.
USE master GO ALTER DATABASE BPO SET EMERGENCY GO
Once the database is in EMERGENCY mode you will be able to query the database. However, your database is not yet out of trouble. Click the Next Page link at the bottom to fix the database and allow multiple users to connect to the database.
Click on Next Page Button to continue reading rest of the article…
Last Updated On: Mar 25, 2015
Please leave your Valuable Comment or Let us know how this article helped you: