SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com

How to Repair Suspect Database in SQL Server

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.

Steps to Fix the SQL Server Database Suspect Mode Error

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 all higher versions.

Learn How to Repair Suspect Database in SQL Server

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.

SUSPECT Database in SQL Server
SUSPECT Database in SQL Server

SQL Server Disaster Recovery Tips

Steps to Repair Suspect Database in SQL Server are:

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
SQL Server SUSPECT Database

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.

If you have database backups which allows you Point in Time Recovery then it is always recommended to restore your database from the available SQL Server Database Backups. However, do consider you have enough space available on the server to restore the database.

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.

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.

USE master
GO

ALTER DATABASE BPO SET EMERGENCY
GO

Once the database is in EMERGENCY mode you will be able to query the database.

SQL Server Database in Emergency Mode
SQL Server Database in Emergency Mode

4. Execute the DBCC CHECKDB command which will check the logical and physical integrity of all the objects within the specified database.

DBCC CHECKDB (BPO)
GO

DBCC CHECKDB will take time depending upon the size of the database. Its always recommended to run DBCC CHECKDB as part of your regular maintenance schedule for all the SQL Server Databases.

5. Next step will be to bring the user database in SINGLE_USER mode by executing the below mentioned TSQL code.

ALTER DATABASE BPO SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

When you repair your database using REPAIR_ALLOW_DATA_LOSS option of DBCC CHECKDB command there can be some loss of data.

Once the database is successfully repaired using REPAIR_ALLOW_DATA_LOSS option of DBCC CHECKDB command then there is no way to go back to the previous state.

6. Once the database is in SINGLE_USER mode execute the below TSQL code to repair the database.

DBCC CHECKDB (BPO, REPAIR_ALLOW_DATA_LOSS)
GO

7. Finally, execute the below mentioned TSQL command to allow MULTI_USER access to the database.

ALTER DATABASE BPO SET MULTI_USER
GO
Fix: SQL Server Database from SUSPECT Mode
Fix: SQL Server Database from SUSPECT Mode

Conclusion

This articles mentions the steps a DBA need to follow to recover database which is marked SUSPECT. As mentioned above, executing DBCC CHECKDBREPAIR_ALLOW_DATA_LOSS DBCC Command will result in Data Loss. Therefore, if you have database backups which will allow “Point In Time” and have enough space on the server then it is the recommended approach.

Top Trending SQL Server DBA and Developer Articles and Tips

Chetna Bhalla

Chetna Bhalla

LESS ME MORE WE

Chetna Bhalla, the founder of MyTechMantra.com, believes that by sharing knowledge and building communities, we can make this world a better place to live in. Chetna is a Graduate in Social Sciences and a Masters in Human Resources and International Business. She is an alumnus of Vignana Jyothi Institute of Management, Hyderabad, India. After graduation, Chetna founded this website, which has since then become quite a favorite in the tech world. Her vision is to make this website the favorite place for seeking information on Databases and other Information Technology areas. She believes that companies which can organize and deploy their data to frame strategies are going to have a competitive edge over others. Her interest areas include Microsoft SQL Server and overall Database Management. Apart from her work, Chetna enjoys spending time with her friends, painting, gardening, playing the violin, and spending time with her son.

Add comment

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us...

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Recent SQL Server Tips

Manning