Printed 2010/02/08 01:46AM
A guide to recover a database out from Suspect mode
By UTSAB CHATTOPADHYAY, 2009/12/22
One of the worst situations I can imagine for a database professional is to get a call reporting a production database is in a "Suspect" state and the business cannot continue. This is a "codered" situation where DBA needs to bring the database online as soon as possible. In this article, I will formulate couple of steps which may be used as a high level process to handle this situation. The first step is always to inform your customer about the outage before they come back to you. I find most of the times this proactive step becomes a life saver. In this way, you will not bequestioned for the time you will spend to bring the database online. Then refer to the SQL Server error log to find the root cause of the issue. It will give you a clear reason mentioning why the database is in Suspect mode. From SQL Server 2005 onwards, I recommend to use a filter while viewing the log and use "Database name" as the filter criteria. In this way you will only see the logs related tothat particular database and then refer to the latest log/s for the root cause. Up until SQL Server 2000, the only option is reading the log from the latest entry backwards to find the root cause. Assuming you found the reason why database is in suspect mode, now you need to take appropriate step to fix the issue. Here I will discuss some of the possible issues which can put a database in Suspectmode and recommend appropriate resolution. Possibility 1: Some one deleted/misplaced a data/log file when SQL Server was offline and the database can not start because of missing file. The solution is to place the missing data/log file in proper location. The SQL Server Error Log error message will give you the exact name and path of the missing file. Once you place the file execute below commandto bring your database online with no data loss. RESTORE DATABASE WITH RECOVERY Possibility 2: SQL Server could not access or place an exclusive lock on the data or log file while coming online. Typically I experience this when SQL Server is shared with some other tool (like Antivirus), which puts an exclusive lock on the data/log file. To resolve it, use process explorer and kill the filehandler which placed lock on the file. You may want to involve your System Admins to get this step executed. Then execute below command and you will have your database online with no data loss: RESTORE DATABASE WITH RECOVERY Possibility 3: This is a worst case scenario. Database is in suspect because of a corrupted transaction. This is a bad news as you may have to lose data at this point unless youhave a good backup! Also this is the most common case I saw for putting an OLTP database in Suspect mode. The root cause of this issue is actually from a guarantee taken by SQL Server to ensure transaction consistency under fundamental ACID property of RDBMS. The root cause of this issue is most likely SQL server abruptly went down/restarted in the middle of a transaction and while coming back,SQL
Page 2 of 3
server could not complete (commit/rollback) the transaction. At this point, I recommend you to take a decision. If you have a good backup and can restore the database in an acceptable time up to an acceptable point, then go ahead for it. But if restore is not an option at thispoint, then you have to execute below steps: Caution! Below steps will cause you to loose data and hence are extremely dangerous to execute. I recommend trying all other possible options including calling Microsoft Support before executing below steps. 1. Switch the Emergency mode on for the database using below command: ALTER DATABASE SET EMERGENCY; 2. Then execute below command: dbcc checkdb...