How to Recover SQL Server Database from SUSPECT Mode

Stephen West ~ Modified: February 8th, 2016 ~ ~ 1 Minute Reading

This topic has 2 replies, 3 voices, and was last updated 8 years, 9 months ago by Andrew Jackson.

Viewing 3 posts - 1 through 3 (of 3 total)

Author

Posts
February 8, 2016 at 1:09 pm #1100 Score: 0

Stephen West
Moderator

Karma: 4 pts

I am facing the issue on my MS SQL 2008 Database, which is marked as suspect. I have a database backup but it’s not updated (older, more than a week). Is there a any other way to repair suspect SQL Server database.

And can anybody explain what is the cause for database goes to SUSPECT mode?

February 10, 2016 at 12:35 pm #1104 Score: 0
Lincoln Burrows
Moderator

Karma: 16 pts
Some of the basic reasons for SQL Server Database to go into suspect mode are:
- SQL Server Database file or transaction log could have been corrupted
- Unexpected shutdown of SQL Server database
- Database could not be opened due to the lack of disk space
To recover SQL Server database from suspect mode
- At very first change the status of your database
- Then set the SQL Server database in emergency mode
- Check the database for any inconsistency
- Then execute the following query
- Now run the REPAIR_ALLOW_DATA_LOSS
- Change database mode to MULTI USERS
- Refresh your database server and connect to database
Note: take the backup of your database for safety
February 13, 2016 at 1:07 pm #1105 Score: 0
Andrew Jackson
Moderator

Karma: 1 pt
First of all make sure that you do not detach suspected database
Then execute the following query:
```
EXEC sp_resetstatus 'DatabaseName';
ALTER DATABASE DatabaseName SET EMERGENCY
DBCC checkdb('DatabaseName')
ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('DatabaseName', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE DatabaseName SET MULTI_USER
```
Author

Posts

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.